This document provides a summary of MySQL indexes and how to use the EXPLAIN statement to analyze query performance. It defines what indexes are, the different types of indexes like B-tree, hash, and full-text indexes. It also explains concepts like compound indexes, covering indexes, and partial indexes. The document demonstrates how to use the EXPLAIN statement to view and understand a query execution plan, including analyzing the possible and actual indexes used, join types, number of rows examined, and index usage. It provides examples of interpreting EXPLAIN output and analyzing performance bottlenecks.
Adrian Hardy's slides from PHPNW08
Once you have your query returning the correct results, speed becomes an important factor. Speed can either be an issue from the outset, or can creep in as your dataset grows. Understanding the EXPLAIN command is essential to helping you solve and even anticipate slow queries.
Associated video: http://blip.tv/file/1791781
MySQL triggers allow stored programs to be automatically invoked in response to data changes, such as inserts, updates or deletes, on a table. Triggers can be used to monitor tables and take corrective actions when conditions occur. For example, a trigger could update the total salary of a department when a new employee is inserted into the employees table.
Using Optimizer Hints to Improve MySQL Query Performanceoysteing
The document discusses using optimizer hints in MySQL to improve query performance. It covers index hints to influence which indexes the optimizer uses, join order hints to control join order, and subquery hints. New optimizer hints introduced in MySQL 5.7 and 8.0 are also presented, including hints for join strategies, materialized intermediate results, and query block naming. Examples are provided to illustrate how hints can be used and their behavior.
How to Analyze and Tune MySQL Queries for Better Performanceoysteing
The document discusses how to analyze and tune queries for better performance in MySQL. It covers topics like cost-based query optimization in MySQL, tools for monitoring, analyzing and tuning queries, data access and index selection, the join optimizer, subqueries, sorting, and influencing the optimizer. The program agenda outlines these topics and their order.
The MySQL Query Optimizer Explained Through Optimizer Traceoysteing
The document discusses the MySQL query optimizer. It begins by explaining how the optimizer works, including analyzing statistics, determining optimal join orders and access methods. It then describes how the optimizer trace can provide insight into why a particular execution plan was selected. The remainder of the document provides details on the various phases the optimizer goes through, including logical transformations, cost-based optimizations like range analysis and join order selection.
- The document discusses advanced techniques for optimizing MySQL queries, including topics like temporary tables, file sorting, order optimizations, and calculated fields.
- It provides examples of using indexes and index optimizations, explaining concepts like index types, index usage, key lengths, and covering indexes.
- One example shows how to optimize a query involving a calculated year() expression by rewriting the query to use a range on the date field instead.
[Pgday.Seoul 2021] 2. Porting Oracle UDF and OptimizationPgDay.Seoul
The document discusses porting functions from Oracle to PostgreSQL and optimizing performance, including different function types in PostgreSQL like SQL functions and PL/pgSQL functions, as well as volatility categories. It also provides examples of test data created for use in examples and covers strategies for analyzing inefficient Oracle functions and improving them to leverage the PostgreSQL optimizer.
The document provides guidance on understanding and optimizing database performance. It emphasizes the importance of properly designing schemas, normalizing data, using appropriate data types, and creating useful indexes. Explain plans should be used to test queries and identify optimization opportunities like adding missing indexes. Overall, the document encourages developers to view the database as a collaborative "friend" rather than an enemy, by understanding its capabilities and limitations.
All about Zookeeper and ClickHouse Keeper.pdfAltinity Ltd
ClickHouse clusters depend on ZooKeeper to handle replication and distributed DDL commands. In this Altinity webinar, we’ll explain why ZooKeeper is necessary, how it works, and introduce the new built-in replacement named ClickHouse Keeper. You’ll learn practical tips to care for ZooKeeper in sickness and health. You’ll also learn how/when to use ClickHouse Keeper. We will share our recommendations for keeping that happy as well.
This document provides tips for tuning a MySQL database to optimize performance. It discusses why tuning is important for cost effectiveness, performance, and competitive advantage. It outlines who should be involved in tuning including application designers, developers, DBAs and system administrators. The document covers what can be tuned such as applications, databases structures, and hardware. It provides best practices for when and how much to tune a database. Specific tuning techniques are discussed for various areas including application development, database design, server configuration, and storage engine optimizations.
Migration to ClickHouse. Practical guide, by Alexander ZaitsevAltinity Ltd
This document provides a summary of migrating to ClickHouse for analytics use cases. It discusses the author's background and company's requirements, including ingesting 10 billion events per day and retaining data for 3 months. It evaluates ClickHouse limitations and provides recommendations on schema design, data ingestion, sharding, and SQL. Example queries demonstrate ClickHouse performance on large datasets. The document outlines the company's migration timeline and challenges addressed. It concludes with potential future integrations between ClickHouse and MySQL.
The document discusses various SQL statements and concepts. It introduces the different types of SQL statements - DQL, DML, DDL, TCL, DCL and describes common statements like SELECT, INSERT, UPDATE, DELETE. It also covers SQL concepts like data types, NULL values, joins, aggregation, sorting, filtering using WHERE clause and logical operators. Single-row functions for character, number and date manipulations are explained along with examples.
A SQL subquery is a query nested inside another query. Subqueries can be used in the SELECT, WHERE, INSERT, UPDATE, or DELETE clauses. The subquery executes first and its results are then used by the outer query. There are three types of subqueries: single row, multiple row, and multiple column. Single row subqueries use comparison operators like =, <, > and return one row. Multiple row subqueries use operators like IN, ANY, ALL and return multiple rows. Multiple column subqueries compare more than one column between the outer and inner queries.
Part 3 of the SQL Tuning workshop examines the different aspects of an execution plan, from cardinality estimates to parallel execution and explains what information you should be gleaming from the plan and how it affects the execution. It offers insight into what caused the Optimizer to make the decision it did as well as a set of corrective measures that can be used to improve each aspect of the plan.
This document provides an overview of visual SQL tuning (VST) methodology. It discusses finding problem SQL, studying the execution plan, and fixing performance issues. Key steps include identifying slow queries, analyzing the execution plan to understand how the SQL is executing, and then fixing issues such as missing indexes or statistics. The document emphasizes drawing a map of tables and joins to visualize the query structure and help determine join order and filtering. It covers techniques for two, three, and four table joins, including different join types and applying filters.
[BITMAP INDEX]
정보저장의 최소단위인 비트를 이용하여 칼럼 값을 간결하게 저장하고 이를 이용하여 자동으로 ROWID를 생성하는 구조를 가지며 분포도가 나쁜 칼럼, NOT, OR를 사용하는 경우 탁월한 성능을 낸다.
비트맵 인덱스를 생성하면 비트리 인덱스처럼 트리구조를 만들고 리프블럭에 값들을 비트로 변환하여 저장한다. 비트리 인덱스의 리프 블록(Leaf Block)은 INDEX KEY VALUE와 ROWID 로 구성이 되어 있지만 비트맵 인덱스는 START ROWID~END ROWID로 압축해서 저장하고 칼럼값 역시 ‘1’ 이라는 비트로 저장해서 원본 데이터의 ROWID를 계산한다.
인덱스를 Bit 단위로 저장(데이터의 존재 여부를 0 or 1로 표시)하고 비트리 인덱스 한계를 극복하여 대량의 자료 조회에 적합한 구조이지만 잦은 DML이 발생되는 곳은 리프 블록(Leaf Block)의 갱신으로 인해 부적합하다. 하나의 인덱스 값을 수정하면 그 인덱스 값을 가지는 모든 로우에 락을 건다. 즉 하나의 인덱스 값으로 테이블상의 여러 개의 행을 표현하기 때문에 INSERT, UPDATE, DELETE 등을 사용하는 경우 오라클 락 메커니즘인 행 단위 락(ROW LEVEL LOCKING)을 지원할 수 없다.
B*Tree 인덱스가 NULL값을 보관하지 않는 것과는 달리 Bitmap 인덱스는 NULL값에 대한 BIT값을 저장하여 비트리 인덱스의 NULL문제를 해결했으며 AND, OR 연산시 비트연산을 빠르게 수행한다.
생성 절차 : 인덱스를 생성하고자 하는 테이블 스캔을 한 후 Bitmap Index Generator에 의해 칼럼 값(비트형태의 ‘1’로 저장), 시작 ROWID, 끝 ROWID , Bitmap을 갖는 인덱스 엔트리를 생성한다. 생성된 Bitmap들을 B-tree구조에 넣기 쉽도록 key값과 start rowid 순으로 정렬하며 마지막 단계에서는 정렬된 인덱스 엔트리들을 단순히 B-tree구조로 삽입한다.
탑크리에듀교육센터(topcredu.co.kr)에서 제공하는 자료입니다.
SQL초보에서 Schema Objects까지 25번째 자료입니다.
단일/복합(결합) 인덱스(Single Column/Composite Index),고유/비고유 인덱스(Unique/Non Unique Index), Descending Index, 함수기반 인덱스(Function Based Index), 인덱스 재구성 및 삭제, 인덱스 숨기기(Index Invisible)에 대하여 설명한 자료이오니 확인 후 많은 도움 되셨길 바랍니다^^.
DynamoDB를 게임에서 사용하기 – 김성수, 박경표, AWS솔루션즈 아키텍트:: AWS Summit Online Korea 2020Amazon Web Services Korea
발표영상 다시보기: https://youtu.be/AlDhGn_-OCE
Amazon DynamoDB를 사용함에 있어서 흔히들 빠지는 실수, 그리고 이를 만회하기 위하여 필요한 제반 요소들을 살펴보는 시간을 가져보겠습니다. 제대로 설계된 DDB 테이블과 Application이 그렇지 않은 경우에 비하여 얼마나 효율적일수 있는지를 생각해봅니다.
-- 먼저 EMP에 테이블에 생성되어 있는 인덱스 및 칼럼을 확인하자.
SQL> SELECT a.index_name, a.column_name, b.visibility
FROM user_ind_columns a, user_indexes b
WHERE a.table_name = 'EMP'
AND a.index_name = b.index_name ;
-- 인덱스가 없다면 생성, 있으면 SKIP
SQL> CREATE INDEX idx_emp_job ON EMP(job);
SQL> CREATE INDEX idx_emp_deptno ON EMP(deptno);
FILTER 연산은 데이터 추출 시 필터링이 일어나고 있음을 알려주는 SQL ROW 연산인데 WHERE 조건 절에서 인덱스를 사용하지 못할 때 발생한다. NESTED LOOP 방식으로 해석할 수 있는데 서브쿼리라면 메인쿼리 로우를 하나씩 읽을때 마다 서브쿼리를 한 번씩 실행하는 형태이다.
FILTER OPERATION은 IN, NOT IN, EXISTS, NOT EXISTS 를 사용하는 경우 발견할 수 있는 OPERATION이며 중첩 루프조인(Nedted Loop Join)과 유사하게 움직인다. 메인쿼리의 결과에 대해 서브쿼리의 결과값을 버퍼에 임시저장해 같거나 다른 것을 찾아 나가는 방식이다. 이러한 과정이 드라이빙되는 테이블의 각 로우에 대해 일어나기 때문에 NESTED LOOP JOIN과 유사하다고 볼 수있다.
Similar to [2015-06-26] Oracle 성능 최적화 및 품질 고도화 3 (20)
This document discusses different architectures for running containers and Kubernetes on AWS. It begins by showing a simple VPC configuration and then expands on it by adding availability zones, load balancing, auto scaling, and Route53 for high availability. It then discusses using ECS or EKS instead of manually launching EC2 instances. The document also provides links about Docker for beginners and how Kubernetes works on EKS, including concepts like clusters, pods, deployments, and services. It concludes by thanking the audience.
The document discusses the differences between static servers, cloud servers, Docker, and Kubernetes. It provides an overview of key concepts in cloud computing including EC2, ECS, EKS, VPCs, subnets, security groups, containers, pods, deployments, services, and nodes. Diagrams illustrate the architecture and relationships between these components in an AWS cloud environment.
AWS DEV DAY SEOUL 2017 Buliding Serverless Web App - 직방 Image ConverterSeok-joon Yun
This document discusses using Amazon Web Services (AWS) to build a serverless web application. It describes deploying an Express.js web framework application on AWS Lambda and API Gateway without a server. The application uses services like AWS Lambda, API Gateway, AWS RDS, AWS S3, and CloudFront. It provides steps for configuring the Express.js app to run on AWS Lambda and calling it through API Gateway.
Pro typescript.ch03.Object Orientation in TypeScriptSeok-joon Yun
The document discusses object-oriented programming concepts in TypeScript such as classes, inheritance, polymorphism, and mixins. It provides code examples of implementing interfaces and classes to demonstrate inheritance and composition. Mixins are discussed as a way to reuse behavior across class hierarchies by applying multiple base classes to a derived class using a mixin function. The examples show how to define mixin behaviors as classes and apply them to implementing classes to achieve multiple inheritance in TypeScript.
The document discusses new C++11 features including:
1. Uniform initialization syntax using curly braces {} which can initialize objects in a clear and consistent way compared to parentheses () or equals =.
2. Initializer lists and how they allow initializing objects from a list of values. However, initializer lists may prefer certain constructors unintuitively.
3. How uniform initialization helps prevent narrowing conversions and most vexing parse issues that could occur in C++98 code.
2. - 1일차 : Tuning 도구
- D/B Tuning이 어려운 이유
- Oracle D/B 구조
- Tuning Tools : AutoTrace, SQL*Trace, V$SQLAREA
- 2일차 : SQL Tuning #1 : Optimizer
- Rule-based Optimizer
- Cost-based Optimizer
- 통계정보, Histogram
- Hint
- 3일차 : SQL Tuning #2 : Index, Join
- Index
- Join
- 4일차 : Server Tuning
- Shared pool tuning
- Data buffer cache tuning
* NoSQL 소개
3. INDEX 설계 시 주의사항
1. 조건에 만족하는 Data가 전체의 10% 내외일때 가장 이상적
2. 대용량 Data를 가진 Table에 적용
( 20 ~ 30만건 이상인 경우는 적극적 고려)
3. 분포도가 나쁘더라도 Fast Index Scan이 가능한 경우 효과적
4. 여러가지 Index 중 적합한 Index로 생성해야 함
5. Index가 생성되는 Tablespace와 물리적 크기에 대해 고려
(Extent, Block 크기)
4. Single Column INDEX vs Composite Index
SELECT * FROM BIG_EMP WHERE DEPTNO = 10 AND JOB = 'CLERK';
CREATE INDEX I_BIG_EMP_DEPTNO ON BIG_EMP(DEPTNO);
CREATE INDEX I_BIG_EMP_JOB ON BIG_EMP(JOB);
CREATE INDEX I_BIG_EMP_DEPTNO_JOB ON BIG_EMP(DEPTNO, JOB);
5. Composite INDEX 선행 Column 결정 기준
1. WHERE 절에서 자주 검색될수록
2. 분포도가 좋을수록
3. Data가 적을수록
4. 범위검색 (BETWEEN, >, <, LIKE)로 검색되지 않을 수록
6. Composite INDEX 선행 Column 결정 기준 (예제 )
CREATE INDEX I_BIG_EMP_DEPTNO_HIREDATE
ON BIG_EMP(DEPTNO, JOB, HIREDATE);
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.001 0 0 0 0
Fetch 2 0.000 0.000 0 137 0 3
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.000 0.001 0 137 0 3
Rows Row Source Operation
------- -----------------------------------------------------------------------
3 SORT ORDER BY (cr=137 pr=0 pw=0 time=0 us cost=0 size=0 card=0)
3 SORT GROUP BY (cr=137 pr=0 pw=0 time=2 us)
131 CONCATENATION (cr=137 pr=0 pw=0 time=390 us)
53 TABLE ACCESS BY INDEX ROWID BIG_EMP (cr=55 pr=0 pw=0 time=208 us)
53 INDEX RANGE SCAN I_BIG_EMP_DEPTNO_HIREDATE (cr=2 pr=0 pw=0 time=52 us)
50 TABLE ACCESS BY INDEX ROWID BIG_EMP (cr=52 pr=0 pw=0 time=98 us)
50 INDEX RANGE SCAN I_BIG_EMP_DEPTNO_HIREDATE (cr=2 pr=0 pw=0 time=49 us)
28 TABLE ACCESS BY INDEX ROWID BIG_EMP (cr=30 pr=0 pw=0 time=27 us)
28 INDEX RANGE SCAN I_BIG_EMP_DEPTNO_HIREDATE (cr=2 pr=0 pw=0 time=27 us)
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.003 0 15 0 0
Fetch 2 0.016 0.057 118 8062 0 3
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.016 0.060 118 8077 0 3
Rows Row Source Operation
------- -----------------------------------------------------------------------
3 SORT ORDER BY (cr=8062 pr=118 pw=0 time=0 us cost=0 size=0 card=0)
3 SORT GROUP BY (cr=8062 pr=118 pw=0 time=0 us)
131 TABLE ACCESS BY INDEX ROWID BIG_EMP (cr=8062 pr=118 pw=0 time=195 us)
28281 INDEX RANGE SCAN I_BIG_EMP_DEPTNO_HIREDATE (cr=119 pr=118 pw=0 time=293037 us)
CREATE INDEX I_BIG_EMP_DEPTNO_HIREDATE
ON BIG_EMP(HIREDATE, DEPTNO, JOB);
SELECT DEPTNO, COUNT(*), SUM(SAL)
FROM BIG_EMP
WHERE HIREDATE BETWEEN '1983-01,01' AND '1984-12-31'
AND DEPTNO IN ( 70, 80 , 90 )
GROUP BY DEPTNO
ORDER BY SUM(SAL) DESC;
7. INDEX를 사용하지 못하는 경우
1. Column을 함수로 변경
(SQL문을 적절히 바꾸던가… 아니면 Function-based Index를 사용하던가)
2. 부정문으로 비교 (<>, !=, ^=, NOT =, NOT BETWEEN, NOT LIKE)
( NOT EXISTS (SELECT 1 FROM … WHERE … ) 로 대체 가능 )
3. IS NULL, IS NOT NULL로 비교
(Index에 NULL값은 없다. 즉 모든 값이 NOT NULL)
(IN NOT NULL 대신 > ‘ ’ 이런식의 존재하는 한 무조건 만족하는 조건으로 검색을 할 수 있다.)
4. LIKE 연산자에서 %(와일드카드)가 앞에 있는 경우
8. Function-based INDEX
1. Oracle 8i 부터 지원
2. WHERE의 함수를 적용한 Column 자체를 INDEX로 생성
3. Cost-based Optimizer에서만 사용이 가능
SELECT * FROM BIG_EMP WHERE NVL(SAL,0) < 4000;
CREATE INDEX I_BIG_EMP_SAL ON BIG_EMP(SAL) CREATE INDEX I_BIG_EMP_NVL_SAL ON BIG_EMP(NVL(SAL,0))
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 225 0.016 0.018 0 408 0 22395
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 227 0.016 0.019 0 408 0 22395
Rows Row Source Operation
------- -----------------------------------------------------------------------
22395 TABLE ACCESS FULL BIG_EMP (cr=408 pr=0 pw=0 time=6177 us
cost=52 size=52128 card=1448)
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.009 0 2 0 0
Fetch 225 0.031 0.052 48 22098 0 22395
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 227 0.031 0.061 48 22100 0 22395
Rows Row Source Operation
------- -----------------------------------------------------------------------
22395 TABLE ACCESS BY INDEX ROWID BIG_EMP (cr=22098 pr=48 pw=0 time=73745 us
cost=50 size=52128 card=1448)
22395 INDEX RANGE SCAN I_BIG_EMP_NVL_SAL (cr=273 pr=48 pw=0 time=51442 us
cost=2 size=0 card=261)
9. Full Table Scan의 성능을 개선시키기 위한 솔루션
1. DB_FILE_MULTIBLOCK_READ_COUNT = 128 block
(라고 설정은 되어 있으나 실제로는 80 ~ 90 정도 성능)
2. Parellel-Query-Option
(Server Processor 가 Parallel query processor들을 생성하여 동시에 읽음)
10. INDEX 생성
CREATE INDEX 를 실행하면…
-> Index 구성 Column 들의 Data를 Sorting 하고, Min, Mid Max 값 계산
-> 최초 Extent 생성 : 첫번째 Block에 Root Block 생성하고 Mid값 저장
-> 다음 Block부터 Sorting 된 Data들 저장 (Leaf Block)
-> Leaf Block의 정보를 저장하고 있는 Block 생성 (Branch Block)
- 최초 생성되는 Leaf Block 들 : Level 1
-> 새로 Data가 추가되었는데 Leaf Block이 꽉 찬 경우 ?
-> 해당 Leaf Block에 중간값을 두고, 양쪽으로 새로운 Leaf Block을 생성 : Level 2
-> 이런 식으로 Depth (Level)이 깊어질 수록 성능이 안좋아진다.
11. INDEX Balancing 분석
ANALYZE INDEX [index_name] VALIDATE STRUCTURE;
SELECT (DEL_LF_ROWS_LEN / LF_ROWS_LEN) * 100 AS BALANCING
FROM INDEX_STATS;
* INDEX_STATS
• INDEX 현재 구조 분석
• Block 수
• 삭제된 행의 길이
• INDEX DEPTH (HEIGHT)
12. INDEX 재구성
ALTER INDEX [index_name] REBUILD [NOLOGGING]
TABLESPACE [tablespace_name];
• Balancing이 20% 초과하면 성능 저하가 심하므로 재구성 하는게 좋음 (사후조치)
(그럼 사전조치는 ? 물리적 설계 : 빈번한 DML 고려)
• INDEX 삭제 후 재 생성
• On-Line rebuild 가능 (DBMS_REDEFINITION Package)
• Tablespace 위치 변경도 가능
13. INDEX 합병 (B*Tree)
ANALYZE INDEX [index_name] COALESCE;
SELECT INDEX_NAME, LEAF_BLOCKS
FROM USER_INDEXES;
SELECT NAME, BLOCKS, LF_BLKS, BR_BLKS
FROM INDEX_STATS;
• INDEX Key 값을 재 배치
• 불필요한 Block 검색 및 공간 낭비를 방지
• 하지만 빈 Node를 반환해주지는 않음
14. INDEX 압축 (B*Tree, IOT)
SELECT INDEX_NAME, COMPRESSION
FROM USER_INDEXES;
• 디스크 공간 절약I/O를 줄일 수 있으므로 검색속도 향상
(하지만 Transaction이 조금만 증가해도 느려질 수 있다)
• 대용량 Data에 적용하면 효율적
• Non-Partitioned Index에만 적용 가능
CREATE INDEX [index_name] ON [table_name]([column_name,...]) [ COMPRESS | NOCOMPRESS ];
15. INDEX Monitoring
• INDEX는 SELECT의 성능을 올려주지만,
DML 들의 성능을 떨어트린다.
• Optimizer가 안 쓰는 INDEX는 과감히 삭제하자.
• 9i부터 가능
• 사용되지 않은 Index를 검색하여 삭제 가능
• Table 당 Index를 32개까지 생성은 가능하나,
Index가 많을 수록 DML문의 성능이 저하됨
ALTER INDEX [index_name] [ MONITORING | NOMONITORING ] USAGE;
SELECT INDEX_NAME, USED
FROM V$OBJECT_USAGE;
16. INDEX 분석용 Script
SELECT X.TABLE_NAME, X.NUM_ROWS,
A.INDEX_NAME, A.TABLESPACE_NAME,
DECODE(A.UNIQUENESS, 'UNIQUE','U','N') "UNIQUE",
B.COLUMN_NAME,
Y.NUM_DISTINCT,
A.PCT_FREE, A.CLUSTERING_FACTOR, A.BLEVEL, A.LEAF_BLOCKS,
(C.DEL_LF_ROWS_LEN / C.LF_ROWS_LEN) * 100 BALANCE
FROM DBA_TABLES X, DBA_TAB_COLUMNS Y, DBA_INDEXES A, DBA_IND_COLUMNS B, INDEX_STATS C
WHERE X.OWNER = 'SCOTT'
AND A.TABLE_OWNER = X.OWNER AND A.TABLE_NAME = X.TABLE_NAME
AND A.TABLE_OWNER = B.INDEX_OWNER AND A.TABLE_NAME = B.TABLE_NAME
AND X.OWNER = Y.OWNER AND X.TABLE_NAME = Y.TABLE_NAME
AND B.COLUMN_NAME = Y.COLUMN_NAME
AND B.INDEX_NAME = C.NAME(+)
ORDER BY X.TABLE_NAME
17. Join 종류
Join description
Natural Join 결합조건이 = 인 일반적인 Join
조건에 맞는 것만 출력
( = Equal Join , Inner Join )
Cross Join 결합조건이 없는 Join
전체와 전체를 다 묶음 (묻지마 Join)
( = Catesian Product )
Self-reference Join 하나의 Table을 다른 Alias를 붙여서 Join
(마치 다른 Table인 듯 취급)
Outer Join 기준이 되는 Table은 결합조건에 맞지 않아도 출력
1. 문법적 측면
18. Join 종류
Join description
Sort-Merge Join
/*+USE_MERGE(a b)*/
Join에 참여하는 Column에 Index가 없을 경우
Full Table Scan -> 그 결과를 Sort한 후 Merge
(즉 3번의 Load가 필요함)
가장 느린 방법
Nested-Loops Join
/*+USE_NL(a b)*/
Index를 이용하여 Join
Cluster Join Index보다 성능이 좋은 Cluster를 이용하여 Join
Hash Join
/*+HASH(a b)*/
v7.3.3부터 가능 : SMJ가 넘 느려서 성능 개선을 위해 개발
왠만해서는 SMJ보다 빠름
Optimizer가 SMJ를 선택하는 경우는 거의 없다.
결합조건 별로 Buffer에 파티션을 나누어 Record를 Load
2. 실행적 측면
19. Join 성능에 영향을 주는 요소
1. Driving Table
2. Join 순서
3. Data 량
4. Index 사용여부
WHERE 절의 Check 조건으로 걸러지는 것 기준으로
Data량이 가장 작은 것을 Driving Table로
Driving Table과 결합조건이 있는 Table중
Data량이 가장 작은 것을 Join
만약 결합 조건 중 한쪽만 Index가 있다면,
Join 할 Table을 Index가 있는 Table로 결정
Driving Table 결정 원리 (Rule-based Optimizer)
- Join에 참여하는 Column에 모두 INDEX가 있거나 없는 경우 (동등조건) : FROM에서 먼 쪽부터
-> 단, Single Index와 Composite Index 중에서는 Composite Index부터,
-> Unique Index와 Non-unique Index 중에서는 Unique Index 부터,
- Outer Join은 Data가 있는 쪽부터
- /*+ORDERED*/ 하면 FROM에서 가까운 쪽부터
- /*+LEADING(A)*/ 하면 해당 Table 부터