Firstly, I'll create a view that does nothing but rearrange the columns in a table. A single table view might be used where you want a groups of users to only see some columns in a table or where you want to include a pseudo-column derived from one or more of the other columns in the table.
create view lp_view as select first_name, surname, lp_id, gender from lots_of_people;
Again, I'll use a DB Link and the local DUAL table to ensure the query is split between the local and remote databases.
EXPLAIN PLAN SET STATEMENT_ID = 'TEST1' INTO PLAN_TABLE FOR
select lp_id from lots_of_people@loop, (select 1 from dual where rownum = 1) d
where lp_id = 5;
select level, lpad('_',1*(level-1),'_')||operation||'('||options||')' operation,
object_owner||'.'||object_name obj_name,
ltrim(to_char(cardinality,'999,999,999')) no_rows, object_node
link, id, parent_id, other, filter_predicates
from PLAN_TABLE
start with id=0 and statement_id = 'TEST1'
connect by prior id = parent_id and prior statement_id = statement_id
and prior timestamp <= timestamp order by id, position
LEVEL OPERATION OBJ_NAME NO_ROWS LINK ID PARENT_ID
----- ---------------------- --------- ------- ---- ---- ----------
OTHER FILTER_PRE
---------------------------------------------------------------------- ----------
1 SELECT STATEMENT() . 1 0
2 _NESTED LOOPS() . 1 1 0
3 __REMOTE() . 1 LOOP 2 1
SELECT "LP_ID" FROM "LOTS_OF_PEOPLE" "LOTS_OF_PEOPLE" WHERE "LP_ID"=5
3 __VIEW() USERXX. 1 3 1
4 ___COUNT(STOPKEY) . 4 3
ROWNUM=1
5 ____TABLE ACCESS(FULL) SYS.DUAL 8,168 5 4
I've queried the plan table directly to show the OTHER column, containing the query sent to the remote database, which isn't shown by DBMS_XPLAN in 9.2.0.4.
You can see that the optimizer expects one row to be returned by the query on LP_ID. That indicates that it knows about the unique index on that column.
In contrast, we can see a similar query using the remote view
EXPLAIN PLAN SET STATEMENT_ID = 'TEST2' INTO PLAN_TABLE FOR
select lp_id from lp_view@loop, (select 1 from dual where rownum = 1) d
where lp_id = 5;
select level, lpad('_',1*(level-1),'_')||operation||'('||options||')' operation,
object_owner||'.'||object_name obj_name,
ltrim(to_char(cardinality,'999,999,999')) no_rows, object_node
link, id, parent_id, other, filter_predicates
from PLAN_TABLE
start with id=0 and statement_id = 'TEST2'
connect by prior id = parent_id and prior statement_id = statement_id
and prior timestamp <= timestamp order by id, position;
Here it expects 825 rows , or 1% of the table, to be returned by the remote query. The index on the table doesn't show up against the view and so its information is unavailable.
LEVEL OPERATION OBJ_NAME NO_ROWS LINK ID PARENT_ID
----- ------------------------- --------- ------- ---- ---- ----------
OTHER FILTER_PRE
------------------------------------------------------ ----------
1 SELECT STATEMENT() . 825 0
2 _MERGE JOIN(CARTESIAN) . 825 1 0
3 __VIEW() USERXX. 1 2 1
4 ___COUNT(STOPKEY) . 3 2
ROWNUM=1
5 ____TABLE ACCESS(FULL) SYS.DUAL 8,168 4 3
3 __BUFFER(SORT) . 825 5 1
4 ___REMOTE() . 825 LOOP 6 5
SELECT "LP_ID" FROM "LP_VIEW" "LP_VIEW" WHERE "LP_ID"=5
Both queries in the 'OTHER' column will be passed to the remote database for parsing though, and there the view will be resolved against the table and the index can be used. However the fact that the local database doesn't know about the index can mean that the best plan is missed.
Finally, I'll include an extract of the 10053 dump file which, for the table, show the LP_ID column with no nulls and the same number as distinct values as the table's cardinality, plus an index on the column. The view shows no such information.
***********************
Table stats Table: LOTS_OF_PEOPLE Alias: LOTS_OF_PEOPLE
TOTAL :: CDN: 82522 NBLKS: 496 AVG_ROW_LEN: 37
Column: LP_ID Col#: 1 Table: LOTS_OF_PEOPLE Alias: LOTS_OF_PEOPLE
NDV: 82522 NULLS: 0 DENS: 1.2118e-05 LO: 1 HI: 82522
NO HISTOGRAM: #BKT: 1 #VAL: 2
-- Index stats
INDEX NAME: 0 COL#: 2 3
TOTAL :: LVLS: 2 #LB: 485 #DK: 78007 LB/K: 1 DB/K: 1 CLUF: 14253
INDEX NAME: 0 COL#: 1
TOTAL :: LVLS: 1 #LB: 154 #DK: 82522 LB/K: 1 DB/K: 1 CLUF: 489
_OPTIMIZER_PERCENT_PARALLEL = 0
***************************************
SINGLE TABLE ACCESS PATH
TABLE: LOTS_OF_PEOPLE ORIG CDN: 82522 ROUNDED CDN: 1 CMPTD CDN: 1
Access path: tsc Resc: 32 Resp: 32
Access path: index (iff)
Index: 0
TABLE: LOTS_OF_PEOPLE
RSC_CPU: 0 RSC_IO: 11
IX_SEL: 0.0000e+00 TB_SEL: 1.0000e+00
Access path: iff Resc: 11 Resp: 11
Access path: index (unique)
Index: 0
TABLE: LOTS_OF_PEOPLE
RSC_CPU: 0 RSC_IO: 1
IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00
Access path: index (eq-unique)
Index: 0
TABLE: LOTS_OF_PEOPLE
RSC_CPU: 0 RSC_IO: 1
IX_SEL: 0.0000e+00 TB_SEL: 0.0000e+00
One row CDN: 1
BEST_CST: 2.00 PATH: 3 Degree: 1
BEST_CST: 8.00 PATH: 2 Degree: 1
***********************
Table stats Table: LP_VIEW Alias: LP_VIEW
TOTAL :: CDN: 82522 NBLKS: 496 AVG_ROW_LEN: 37
_OPTIMIZER_PERCENT_PARALLEL = 0
***************************************
SINGLE TABLE ACCESS PATH
Column: LP_ID Col#: 3 Table: LP_VIEW Alias: LP_VIEW
NO STATISTICS (using defaults)
NDV: 2579 NULLS: 0 DENS: 3.8778e-04
NO HISTOGRAM: #BKT: 0 #VAL: 0
TABLE: LP_VIEW ORIG CDN: 82522 ROUNDED CDN: 825 CMPTD CDN: 825
Access path: tsc Resc: 32 Resp: 32
BEST_CST: 32.00 PATH: 2 Degree: 1
BEST_CST: 8.00 PATH: 2 Degree: 1
No comments:
Post a Comment