Hello,
we develop software which needs some columns ordered not in binary but with NLS_SORT=XGERMAN_DIN;
Some query are orderd by language specific strings.
To increase response time and to save CPU we created linguistics indexes on these columns (legalname)
Following Query should use the index; interesting is only the innerst part of it :
Explain-plan window generates following execution plan :
AS you can see the sorted index on legalname is not used - a fulltable scan on company with sort-operation is done instead.
But it should be used when NLS_SORT is set to XGERMAN_DIN. I did this in SQL-Query window (marking first the ALTER SESSION Statement and then the query to explain).
But the FULL-Table-Scan on Company Table remained.
This raised the suspicion that explain-plan window is executed not in the same session of teh SQL-Window.
With the low level tools it works fine:
As you see the index on Company is used.
I would like to have the Explain-plan window in the same session as the sql-window. Probably the rollback to the plan_table could be done via a procedure with autonomic transaction to have no transactional side effects with the SQL-Windows transaction.
Regards
Carl
we develop software which needs some columns ordered not in binary but with NLS_SORT=XGERMAN_DIN;
Some query are orderd by language specific strings.
To increase response time and to save CPU we created linguistics indexes on these columns (legalname)
Code:
create index COMPANY_FXDE02 on COMPANY (NLSSORT(LEGALNAME,'nls_sort=''XGERMAN_DIN'''))
tablespace ISISDAS
pctfree 10
;
Code:
SELECT /*TEST*/
Company.*,
Isis.Regiontext.VALUE AS Region,
Isis.Legalformtext.VALUE AS Legalform,
Isis.Industrytext.VALUE AS Industry,
Isis.Companystatetext.VALUE AS Companystatetext,
Isis.Countrytext.VALUE AS Countrytext,
Isis.Industrysubtypetext.VALUE AS Industrysubtype
FROM (SELECT *
FROM (SELECT /*+ FIRST_ROWS */
Iq.*,
Rownum AS z_r_n
FROM (SELECT *
FROM Company
--WHERE nls_Upper(TRIM(Legalname)) LIKE nls_Upper(TRIM('MUELL%'))
ORDER BY Company.Legalname ASC) Iq
WHERE Rownum <= 20)
WHERE z_r_n >= 1) Company,
Isis.Regiontext,
Isis.Legalformtext,
Isis.Industrytext,
Isis.Companystatetext,
Isis.Countrytext,
Isis.Industrysubtypetext
WHERE Isis.Regiontext.Key(+) = Company.Regionkey
AND Isis.Regiontext.Locale(+) = 'DE'
AND Isis.Legalformtext.Key(+) = Company.Legalformkey
AND Isis.Legalformtext.Locale(+) = 'DE'
AND Isis.Industrytext.Key(+) = Company.Industrykey
AND Isis.Industrytext.Locale(+) = 'DE'
AND Isis.Companystatetext.Key(+) = Company.Companystate
AND Isis.Companystatetext.Locale(+) = 'DE'
AND Isis.Countrytext.Key(+) = Company.Countryisocode
AND Isis.Countrytext.Locale(+) = 'DE'
AND Isis.Industrysubtypetext.Key(+) = Company.Industrysubtypekey
AND Isis.Industrysubtypetext.Locale(+) = 'DE'
Code:
SELECT STATEMENT, GOAL = HINT: FIRST_ROWS Cost=569 Cardinality=32 Bytes=26688
HASH JOIN OUTER Cost=569 Cardinality=32 Bytes=26688
NESTED LOOPS OUTER Cost=566 Cardinality=26 Bytes=21216
NESTED LOOPS OUTER Cost=546 Cardinality=20 Bytes=15920
NESTED LOOPS OUTER Cost=526 Cardinality=20 Bytes=15580
NESTED LOOPS OUTER Cost=506 Cardinality=20 Bytes=15120
NESTED LOOPS OUTER Cost=486 Cardinality=20 Bytes=14720
VIEW Object owner=ISIS Cost=466 Cardinality=20 Bytes=14060
COUNT STOPKEY
VIEW Object owner=ISIS Cost=466 Cardinality=25637 Bytes=17689530
SORT ORDER BY STOPKEY Cost=466 Cardinality=25637 Bytes=2768796
TABLE ACCESS FULL Object owner=ISIS Object name=COMPANY Cost=37 Cardinality=25637 Bytes=2768796
TABLE ACCESS BY INDEX ROWID Object owner=ISIS Object name=INDUSTRYTEXT Cost=1 Cardinality=1 Bytes=33
INDEX UNIQUE SCAN Object owner=ISIS Object name=INDUSTRYTEXT_UK10 Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=ISIS Object name=REGIONTEXT Cost=1 Cardinality=1 Bytes=20
INDEX UNIQUE SCAN Object owner=ISIS Object name=REGIONTEXT_UK13 Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=ISIS Object name=INDUSTRYSUBTYPETEXT Cost=1 Cardinality=1 Bytes=23
INDEX UNIQUE SCAN Object owner=ISIS Object name=INDUSTRYSUBTYPETEXT_UK11 Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=ISIS Object name=LEGALFORMTEXT Cost=1 Cardinality=1 Bytes=17
INDEX UNIQUE SCAN Object owner=ISIS Object name=LEGALFORMTEXT_UK12 Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=ISIS Object name=COUNTRYTEXT Cost=1 Cardinality=1 Bytes=20
INDEX UNIQUE SCAN Object owner=ISIS Object name=COUNTRYTEXT_UK9 Cardinality=1
TABLE ACCESS FULL Object owner=ISIS Object name=COMPANYSTATETEXT Cost=2 Cardinality=6 Bytes=108But it should be used when NLS_SORT is set to XGERMAN_DIN. I did this in SQL-Query window (marking first the ALTER SESSION Statement and then the query to explain).
But the FULL-Table-Scan on Company Table remained.
This raised the suspicion that explain-plan window is executed not in the same session of teh SQL-Window.
With the low level tools it works fine:
Code:
ALTER SESSION SET NLS_SORT=XGERMAN_DIN;
EXPLAIN PLAN FOR SELECT /*TEST2*/
Company.*,
Isis.Regiontext.VALUE AS Region,
Isis.Legalformtext.VALUE AS Legalform,
Isis.Industrytext.VALUE AS Industry,
Isis.Companystatetext.VALUE AS Companystatetext,
Isis.Countrytext.VALUE AS Countrytext,
Isis.Industrysubtypetext.VALUE AS Industrysubtype
FROM (SELECT *
FROM (SELECT /*+ FIRST_ROWS */
Iq.*,
Rownum AS z_r_n
FROM (SELECT *
FROM Company
--WHERE nls_Upper(TRIM(Legalname)) LIKE nls_Upper(TRIM('MUELL%'))
ORDER BY Company.Legalname ASC) Iq
WHERE Rownum <= 20)
WHERE z_r_n >= 1) Company,
Isis.Regiontext,
Isis.Legalformtext,
Isis.Industrytext,
Isis.Companystatetext,
Isis.Countrytext,
Isis.Industrysubtypetext
WHERE Isis.Regiontext.Key(+) = Company.Regionkey
AND Isis.Regiontext.Locale(+) = 'DE'
AND Isis.Legalformtext.Key(+) = Company.Legalformkey
AND Isis.Legalformtext.Locale(+) = 'DE'
AND Isis.Industrytext.Key(+) = Company.Industrykey
AND Isis.Industrytext.Locale(+) = 'DE'
AND Isis.Companystatetext.Key(+) = Company.Companystate
AND Isis.Companystatetext.Locale(+) = 'DE'
AND Isis.Countrytext.Key(+) = Company.Countryisocode
AND Isis.Countrytext.Locale(+) = 'DE'
AND Isis.Industrysubtypetext.Key(+) = Company.Industrysubtypekey
AND Isis.Industrysubtypetext.Locale(+) = 'DE';
select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));
ROLLBACK;
SQL>
Session altered
Explained
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 32
|* 1 | HASH JOIN OUTER | | 32
| 2 | NESTED LOOPS OUTER | | 26
| 3 | NESTED LOOPS OUTER | | 20
| 4 | NESTED LOOPS OUTER | | 20
| 5 | NESTED LOOPS OUTER | | 20
| 6 | NESTED LOOPS OUTER | | 20
|* 7 | VIEW | | 20
|* 8 | COUNT STOPKEY | |
| 9 | VIEW | | 25637
| 10 | TABLE ACCESS BY INDEX ROWID| COMPANY | 25637
| 11 | INDEX FULL SCAN | COMPANY_FXDE02 | 25637
| 12 | TABLE ACCESS BY INDEX ROWID | INDUSTRYTEXT | 1
|* 13 | INDEX UNIQUE SCAN | INDUSTRYTEXT_UK10 | 1
| 14 | TABLE ACCESS BY INDEX ROWID | REGIONTEXT | 1
|* 15 | INDEX UNIQUE SCAN | REGIONTEXT_UK13 | 1
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 16 | TABLE ACCESS BY INDEX ROWID | INDUSTRYSUBTYPETEXT | 1
|* 17 | INDEX UNIQUE SCAN | INDUSTRYSUBTYPETEXT_UK11 | 1
| 18 | TABLE ACCESS BY INDEX ROWID | LEGALFORMTEXT | 1
|* 19 | INDEX UNIQUE SCAN | LEGALFORMTEXT_UK12 | 1
| 20 | TABLE ACCESS BY INDEX ROWID | COUNTRYTEXT | 1
|* 21 | INDEX UNIQUE SCAN | COUNTRYTEXT_UK9 | 1
|* 22 | TABLE ACCESS FULL | COMPANYSTATETEXT | 6
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("from$_subquery$_002"."COMPANYSTATE"=TO_NUMBER("COMPANYSTATETEXT".
7 - filter("from$_subquery$_002"."Z_R_N">=1)
8 - filter(ROWNUM<=20)
13 - access("INDUSTRYTEXT"."KEY"(+)="from$_subquery$_002"."INDUSTRYKEY" AND
"INDUSTRYTEXT"."LOCALE"(+)='DE')
15 - access("REGIONTEXT"."KEY"(+)="from$_subquery$_002"."REGIONKEY" AND
"REGIONTEXT"."LOCALE"(+)='DE')
17 - access("INDUSTRYSUBTYPETEXT"."KEY"(+)="from$_subquery$_002"."INDUSTRYSUBT
"INDUSTRYSUBTYPETEXT"."LOCALE"(+)='DE')
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
19 - access("LEGALFORMTEXT"."KEY"(+)="from$_subquery$_002"."LEGALFORMKEY" AND
"LEGALFORMTEXT"."LOCALE"(+)='DE')
21 - access("COUNTRYTEXT"."KEY"(+)="from$_subquery$_002"."COUNTRYISOCODE" AND
"COUNTRYTEXT"."LOCALE"(+)='DE')
22 - filter("COMPANYSTATETEXT"."LOCALE"(+)='DE')
Note: cpu costing is offI would like to have the Explain-plan window in the same session as the sql-window. Probably the rollback to the plan_table could be done via a procedure with autonomic transaction to have no transactional side effects with the SQL-Windows transaction.
Regards
Carl
