Where does Toad display the hint Notes (e.g. for star_transformations) that is shown when asking for the Explain plan?
For easily showing what this enhancement request is about the Oracle schema SH is necessary:
1. Please run 'analyze table' for the schema SH
2. please start a command line SQL*Plus and connect to the schema SH
3. and enter:
- alter session set star_transformation_enabled='true';
- set echo on
- set autotrace traceonly
- set pages 400
- set lines 500
4. then enter:
select sum(quantity_sold), p.prod_subcategory_desc, c.cust_gender from
sh.sales s, sh.products p, sh.customers c
where s.prod_id = p.prod_id
and s.cust_id = c.cust_id
and p.prod_subcategory_desc = 'Memory'
and c.cust_city = 'Oxford'
and c.cust_gender = 'F' group by p.prod_subcategory_desc, c.cust_gender;
And you receive:
Plan hash value: 1298476029
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 685 (2)| 00:00:09 | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6605_5F004DCB | | | | | | |
|* 3 | TABLE ACCESS FULL | CUSTOMERS | 46 | 782 | 332 (2)| 00:00:04 | | |
| 4 | HASH GROUP BY | | 1 | 37 | 353 (2)| 00:00:05 | | |
|* 5 | HASH JOIN | | 1 | 37 | 353 (2)| 00:00:05 | | |
|* 6 | HASH JOIN | | 2 | 60 | 350 (2)| 00:00:05 | | |
|* 7 | TABLE ACCESS FULL | PRODUCTS | 3 | 54 | 3 (0)| 00:00:01 | | |
| 8 | PARTITION RANGE ALL | | 32 | 384 | 347 (2)| 00:00:05 | 1 | 28 |
| 9 | TABLE ACCESS BY LOCAL IN
WORKAROUND:
In Toad this hint isn't shown at the moment and there is no workaround.
STATUS:
Enhancement request ST #57993 has been submitted to Development for consideration in future release of Toad for Oracle.
This oracle feature is only available since oracle 10 and higher.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Conditions d’utilisation Confidentialité Cookie Preference Center