Here is a simple case study :
First I present a summary of the data. BIZ_COUNTRIES is a table listing the countries that this organisation does business in. BIZ_PARTNERS lists it's partners -- it has a separate partner enterprise in each country. PRODUCTS and SALES are self-explanatory. Each of these tables has a Primary Key and Foreign Key constraints have been defined for SALES to BIZ_PARTNERS and PRODUCTS and BIZ_PARTNERS to BIZ_COUNTRIES.
create table BIZ_COUNTRIES
(country_id number not null primary key,
country_name varchar2(50) not null,
country_notes varchar2(200),
biz_start_date date)
/
create table BIZ_PARTNERS
(partner_id number not null primary key,
partner_country_id number not null,
partner_name varchar2(50) not null,
partner_notes varchar2(200),
partner_start_date date)
/
alter table BIZ_PARTNERS
add constraint p_c_fk foreign key (partner_country_id)
references biz_countries(country_id)
/
create table PRODUCTS
(product_id number not null primary key,
product_name varchar2(50) not null)
/
create table SALES
(sales_inv_id number not null primary key,
sale_partner_id number,
sale_product_id number,
sale_quantity number,
sale_date date)
/
alter table SALES
add constraint s_ptnr_fk foreign key (sale_partner_id)
references biz_partners(partner_id)
/
alter table SALES
add constraint s_prd_fk foreign key (sale_product_id)
references products(product_id)
/
create index sales_partner_ndx on sales(sale_partner_id);
create index sales_product on sales(sale_product_id);
SQL> desc BIZ_COUNTRIES
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
COUNTRY_ID NOT NULL NUMBER
COUNTRY_NAME NOT NULL VARCHAR2(50)
COUNTRY_NOTES VARCHAR2(200)
BIZ_START_DATE DATE
SQL> desc BIZ_PARTNERS
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
PARTNER_ID NOT NULL NUMBER
PARTNER_COUNTRY_ID NOT NULL NUMBER
PARTNER_NAME NOT NULL VARCHAR2(50)
PARTNER_NOTES VARCHAR2(200)
PARTNER_START_DATE DATE
SQL> desc PRODUCTS
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
PRODUCT_ID NOT NULL NUMBER
PRODUCT_NAME NOT NULL VARCHAR2(50)
SQL> desc SALES
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
SALES_INV_ID NOT NULL NUMBER
SALE_PARTNER_ID NUMBER
SALE_PRODUCT_ID NUMBER
SALE_QUANTITY NUMBER
SALE_DATE DATE
SQL>
SQL> REM *after* loading data, statistics are gathered as :
exec dbms_stats.gather_table_stats('','BIZ_COUNTRIES',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE 250',cascade=>TRUE);
exec dbms_stats.gather_table_stats('','BIZ_PARTNERS',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE 250',cascade=>TRUE);
exec dbms_stats.gather_table_stats('','PRODUCTS',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE 250',cascade=>TRUE);
exec dbms_stats.gather_table_stats('','SALES',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE 250',cascade=>TRUE);
This is the data present :
SQL> select country_id, country_name from biz_countries order by 1;
COUNTRY_ID COUNTRY_NAME
---------- --------------------------------------------------
1 India
2 Singapore
3 Malaysia
4 Thailand
5 Australia
6 UK
7 USA
8 Brazil
8 rows selected.
SQL> select partner_id, partner_country_id, partner_name from biz_partners order by 1;
PARTNER_ID PARTNER_COUNTRY_ID PARTNER_NAME
---------- ------------------ --------------------------------------------------
1 1 Star Enterprises Pvt Ltd
2 2 LionCity Traders
3 3 Growth Traders
4 4 Rising Pvt Ltd
5 5 Star Enterprises _5_ Inc.
6 6 Star Enterprises _6_ Inc.
7 7 Star Enterprises _7_ Inc.
8 8 Star Enterprises _8_ Inc.
8 rows selected.
SQL> select product_id, product_name from products order by 1;
PRODUCT_ID PRODUCT_NAME
---------- --------------------------------------------------
1 YellowWidgets
2 RedWidgets
3 GreenWidgets
4 BlueWidgets
5 BlackWidgets
6 WhiteWidgets
6 rows selected.
SQL> select count(*) from sales;
COUNT(*)
----------
999999
SQL> select min(sale_date), max(sale_date) from sales;
MIN(SALE_ MAX(SALE_
--------- ---------
01-JAN-07 26-SEP-09
SQL> select sale_partner_id, count(*) from sales group by sale_partner_id;
SALE_PARTNER_ID COUNT(*)
--------------- ----------
1 749999
2 250000
SQL>
Thus, we see that only 2 Partners have made sales for us, and Partner ID 1 ("Star Enterprises Pvt Ltd") accounts for almost 75% of the sales -- all in Country ID 1 ("India").
(We can assume that all the products have the same sale price so we do not need to trace sale price and revenue by product).
Here is the first query where I query for Sales made after 01-Jan-2009 by Partner 1 :
SQL> -- QUERY 1
SQL> select c.country_id, c.country_name, p.partner_id, count(s.sale_date)
2 from biz_countries c, biz_partners p, sales s
3 where c.country_id=p.partner_country_id
4 and p.partner_id=s.sale_partner_id
5 and s.sale_date > to_date('01-JAN-2009','DD-MON-YYYY')
6 and p.partner_name = 'Star Enterprises Pvt Ltd'
7 group by c.country_id, c.country_name, p.partner_id
8 order by 1,2,3
9 /
COUNTRY_ID COUNTRY_NAME PARTNER_ID COUNT(S.SALE_DATE)
---------- -------------------------------------------------- ---------- ------------------
1 India 1 201749
SQL>
SQL> -- let's see the optimizer's cardinality estimates
SQL> explain plan for
2 select c.country_id, c.country_name, p.partner_id, count(s.sale_date)
3 from biz_countries c, biz_partners p, sales s
4 where c.country_id=p.partner_country_id
5 and p.partner_id=s.sale_partner_id
6 and s.sale_date > to_date('01-JAN-2009','DD-MON-YYYY')
7 and p.partner_name = 'Star Enterprises Pvt Ltd'
8 group by c.country_id, c.country_name, p.partner_id
9 order by 1,2,3
10 /
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 473150350
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 32 | 1632 | 1052 (3)| 00:00:13 |
| 1 | SORT GROUP BY | | 32 | 1632 | 1052 (3)| 00:00:13 |
|* 2 | HASH JOIN | | 33625 | 1674K| 1050 (3)| 00:00:13 |
| 3 | NESTED LOOPS | | 1 | 40 | 4 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | BIZ_PARTNERS | 1 | 29 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| BIZ_COUNTRIES | 1 | 11 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | SYS_C006892 | 1 | | 0 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | SALES | 269K| 2889K| 1043 (2)| 00:00:13 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."PARTNER_ID"="S"."SALE_PARTNER_ID")
4 - filter("P"."PARTNER_NAME"='Star Enterprises Pvt Ltd')
6 - access("C"."COUNTRY_ID"="P"."PARTNER_COUNTRY_ID")
7 - filter("S"."SALE_DATE">TO_DATE(' 2009-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
22 rows selected.
SQL>
If you read the Execution Plan carefully you will see that the Optimizer expected to fetch 33,625 rows in the Joins between the three tables and generate 32 distinct groupings in the Group By. The actual result was 201,749 rows and only 1 single grouping !
Even with 100% statistics the optimizer may not be very accurate ?!
What if I rewrite the query :
SQL> explain plan for
2 select c.country_id, c.country_name, p.partner_id, count(s.sale_date)
3 from biz_countries c, biz_partners p, sales s
4 where c.country_id=p.partner_country_id
5 and p.partner_id=s.sale_partner_id
6 and s.sale_date > to_date('01-JAN-2009','DD-MON-YYYY')
7 -- and p.partner_name = 'Star Enterprises Pvt Ltd'
8 and p.partner_id = 1
9 group by c.country_id, c.country_name, p.partner_id
10 order by 1,2,3
11 /
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 574353167
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 32 | 896 | 1048 (3)| 00:00:13 |
| 1 | HASH GROUP BY | | 32 | 896 | 1048 (3)| 00:00:13 |
| 2 | NESTED LOOPS | | 201K| 5516K| 1048 (3)| 00:00:13 |
| 3 | NESTED LOOPS | | 1 | 17 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| BIZ_PARTNERS | 1 | 6 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | SYS_C006896 | 1 | | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| BIZ_COUNTRIES | 8 | 88 | 1 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | SYS_C006892 | 1 | | 0 (0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | SALES | 201K| 2167K| 1046 (3)| 00:00:13 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("P"."PARTNER_ID"=1)
7 - access("C"."COUNTRY_ID"="P"."PARTNER_COUNTRY_ID")
8 - filter("S"."SALE_DATE">TO_DATE(' 2009-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "S"."SALE_PARTNER_ID"=1)
23 rows selected.
SQL>
What did I change ? Instead of supplying the Partner Name, I supplied the Partner ID in my query. That was the only change I made to the query. Yet, if you see the Predicate Information section of the Execution Plan, Oracle has now passed "SALE_PARTNER_ID=1" as one of the filter predicates on the SALES table. My query did not explicitly specify what the SALE_PARTNER_ID was to be. It only specified the PARTNER_ID (from the BIZ_PARTNERS table). Yet, Oracle was able to transform (i.e. rewrite the query) and, instead of merely using a Join on SALE_PARTNER_ID, it decided to apply SALE_PARTNER_ID=1 as a filter against the SALES table !
The Optimizer has also come up with a more accurate estimate of 201K rows to be fetched from the SALES table and also to be the result of the Joins.
Because I knew that the SALE_PARTNER_ID for "Star Enterprises Pvt Ltd" was 1 and that there is no other Partner with the same name (this is the key piece of information !), I could change my query predicate from "and p.partner_name = 'Star Enterprises Pvt Ltd'" to "and p.partner_id = 1". This is based on my knowledge of the data !
Let me look at the second query (very very similar !) :
SQL> -- QUERY 2
SQL> select c.country_id, c.country_name, p.partner_id, count(s.sale_date)
2 from biz_countries c, biz_partners p, sales s
3 where c.country_id=p.partner_country_id
4 and p.partner_id=s.sale_partner_id
5 and s.sale_date > to_date('01-JAN-2009','DD-MON-YYYY')
6 and p.partner_name = 'Star Enterprises _7_ Inc.'
7 group by c.country_id, c.country_name, p.partner_id
8 order by 1,2,3
9 /
no rows selected
SQL>
Partner ID 7 ("Star Enterprises _7_ Inc.") has made no sales.
What does the Optimizer estimate ? :
SQL> explain plan for
2 select c.country_id, c.country_name, p.partner_id, count(s.sale_date)
3 from biz_countries c, biz_partners p, sales s
4 where c.country_id=p.partner_country_id
5 and p.partner_id=s.sale_partner_id
6 and s.sale_date > to_date('01-JAN-2009','DD-MON-YYYY')
7 and p.partner_name = 'Star Enterprises _7_ Inc.'
8 group by c.country_id, c.country_name, p.partner_id
9 order by 1,2,3
10 /
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 473150350
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 32 | 1632 | 1052 (3)| 00:00:13 |
| 1 | SORT GROUP BY | | 32 | 1632 | 1052 (3)| 00:00:13 |
|* 2 | HASH JOIN | | 33625 | 1674K| 1050 (3)| 00:00:13 |
| 3 | NESTED LOOPS | | 1 | 40 | 4 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | BIZ_PARTNERS | 1 | 29 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| BIZ_COUNTRIES | 1 | 11 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | SYS_C006892 | 1 | | 0 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | SALES | 269K| 2889K| 1043 (2)| 00:00:13 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."PARTNER_ID"="S"."SALE_PARTNER_ID")
4 - filter("P"."PARTNER_NAME"='Star Enterprises _7_ Inc.')
6 - access("C"."COUNTRY_ID"="P"."PARTNER_COUNTRY_ID")
7 - filter("S"."SALE_DATE">TO_DATE(' 2009-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
22 rows selected.
SQL>
Isn't that very surprising ? The Optimizer estimates the same number of rows for sales by "Star Enterprises _7_ Inc." (which are actually 0 rows) as it did for sales by "Star Enterprises Pvt Ltd" (which were 201thousand !).
Let me rewrite my query again :
SQL> explain plan for
2 select c.country_id, c.country_name, p.partner_id, count(s.sale_date)
3 from biz_countries c, biz_partners p, sales s
4 where c.country_id=p.partner_country_id
5 and p.partner_id=s.sale_partner_id
6 and s.sale_date > to_date('01-JAN-2009','DD-MON-YYYY')
7 -- and p.partner_name = 'Star Enterprises _7_ Inc.'
8 and p.partner_id = 7
9 group by c.country_id, c.country_name, p.partner_id
10 order by 1,2,3
11 /
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2893611923
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 5 (0)| 00:00:01 |
| 1 | HASH GROUP BY | | 1 | 28 | 5 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 28 | 5 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 17 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| BIZ_PARTNERS | 1 | 6 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | SYS_C006896 | 1 | | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| BIZ_COUNTRIES | 8 | 88 | 1 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | SYS_C006892 | 1 | | 0 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID | SALES | 1 | 11 | 3 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | SALES_PARTNER_NDX | 1 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("P"."PARTNER_ID"=7)
7 - access("C"."COUNTRY_ID"="P"."PARTNER_COUNTRY_ID")
8 - filter("S"."SALE_DATE">TO_DATE(' 2009-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
9 - access("S"."SALE_PARTNER_ID"=7)
24 rows selected.
SQL>
Once again, I used my knowledge of the fact that "Star Enterprises _7_ Inc." appears only once in BIZ_PARTNERS and is PARTNER_ID 7 and replaced the query predicate "and p.partner_name = 'Star Enterprises _7_ Inc.'" with "and p.partner_id = 7". Oracle could add it's own "SALE_PARTNER_ID=7" access predicate on the SALES table.
This time the Optimizer presents 1 row as the estimated row count from SALES (an estimate of 0 gets rounded up to 1 row !) and, correspondingly, a group by result of 1 row. Since it expected only 1 row from SALES, it even avoided a FullTableScan on the SALES table and used the SALE_PARTNER_NDX. The estimated "Cost" of the query comes down from 1052 to 5. (Although, as I have repeatedly said, ignore "Cost" look at the actual execution steps and look at the real effort -- buffer gets, consistent reads, physical reads, elapsed time -- when the query is executed).
So, with prior knowledge of the data, I can rewrite my user's query and supply the proper predicate to allow the optimizer to further improve on my query and add it's own filter on the SALES table !
Also read this post on Transitive Closure by Jonathan Lewis.
.
.
.
No comments:
Post a Comment