Здравствуйте, Овощ, Вы писали:

О>Здравствуйте, Petr.com.

О>DDL, запрос, его план, а заодно и трейс 10053 в студию.

Пожалуйста

CREATE OR REPLACE FORCE VIEW "V_PHYSICAL_UCLIENT" ("UCLIENT_ID", "PHYSICAL_UCLIENT_ID", "LNAME", "FNAME", "PNAME", "BIRTH_DATE", "ADDRESS", "UCLIENT_STATUS", "STATE", "BRANCH_ID") AS
select /*+ use_nl(pc uc)*/ uc.uclient_id, pc.physical_uclient_id, pc.lname, pc.fname, pc.pname, pc.birth_date,
(select case when ar.region is not null then ar.region || ', ' else '' end ||
case when ar.district is not null then ar.district || ', ' else '' end ||
case when ar.city is not null then ar.city || ', ' else '' end ||
case when ar.settlement is not null then ar.settlement || ', ' else '' end ||
case when ar.street is not null then ar.street || ', ' else '' end ||
case when ar.home is not null then ' д.' || ar.home || ', ' else '' end ||
case when ar.home_subnum1 is not null then 'корп.' || ar.home_subnum1 || ', ' else '' end ||
case when ar.home_subnum2 is not null then 'стр.' || ar.home_subnum2 || ', ' else '' end ||
case when ar.apartment is not null then 'кв.' || ar.apartment || ', ' else '' end
from address ar where uc.uclient_id = ar.uclient_id and ar.addr_type_id = 1) address,
(select cs.name from uclient_status cs where uc.status_id = cs.status_id) uclient_status,
nvl(uc.state,0) state, uc.branch_id
from uclient uc inner join physical_uclient pc on uc.uclient_id = pc.uclient_id;

запрос

select * from V_PHYSICAL_UCLIENT order by lname

план (xml export)

<results>
<row>
<Operation><![CDATA[SELECT STATEMENT]]></Operation>
<Optimizer><![CDATA[ALL_ROWS]]></Optimizer>
<Cost><![CDATA[1926395]]></Cost>
<Cardinality><![CDATA[942348]]></Cardinality>
<Bytes><![CDATA[96119496]]></Bytes>
<Partition_Start><![CDATA[]]></Partition_Start>
<Partition_Stop><![CDATA[]]></Partition_Stop>
<Partition_Id><![CDATA[]]></Partition_Id>
<ACCESS_PREDICATES><![CDATA[]]></ACCESS_PREDICATES>
<FILTER_PREDICATES><![CDATA[]]></FILTER_PREDICATES>
</row>
<row>
<Operation><![CDATA[TABLE ACCESS(BY INDEX ROWID) RSHB_UNFR.ADDRESS]]></Operation>
<Optimizer><![CDATA[ANALYZED]]></Optimizer>
<Cost><![CDATA[4]]></Cost>
<Cardinality><![CDATA[1]]></Cardinality>
<Bytes><![CDATA[143]]></Bytes>
<Partition_Start><![CDATA[]]></Partition_Start>
<Partition_Stop><![CDATA[]]></Partition_Stop>
<Partition_Id><![CDATA[]]></Partition_Id>
<ACCESS_PREDICATES><![CDATA[]]></ACCESS_PREDICATES>
<FILTER_PREDICATES><![CDATA["AR"."ADDR_TYPE_ID"=1]]></FILTER_PREDICATES>
</row>
<row>
<Operation><![CDATA[INDEX(RANGE SCAN) RSHB_UNFR.XIF4ADDRESS]]></Operation>
<Optimizer><![CDATA[ANALYZED]]></Optimizer>
<Cost><![CDATA[3]]></Cost>
<Cardinality><![CDATA[2]]></Cardinality>
<Bytes><![CDATA[]]></Bytes>
<Partition_Start><![CDATA[]]></Partition_Start>
<Partition_Stop><![CDATA[]]></Partition_Stop>
<Partition_Id><![CDATA[]]></Partition_Id>
<ACCESS_PREDICATES><![CDATA["AR"."UCLIENT_ID"=:B1]]></ACCESS_PREDICATES>
<FILTER_PREDICATES><![CDATA[]]></FILTER_PREDICATES>
</row>
<row>
<Operation><![CDATA[TABLE ACCESS(BY INDEX ROWID) RSHB_UNFR.UCLIENT_STATUS]]></Operation>
<Optimizer><![CDATA[ANALYZED]]></Optimizer>
<Cost><![CDATA[1]]></Cost>
<Cardinality><![CDATA[1]]></Cardinality>
<Bytes><![CDATA[28]]></Bytes>
<Partition_Start><![CDATA[]]></Partition_Start>
<Partition_Stop><![CDATA[]]></Partition_Stop>
<Partition_Id><![CDATA[]]></Partition_Id>
<ACCESS_PREDICATES><![CDATA[]]></ACCESS_PREDICATES>
<FILTER_PREDICATES><![CDATA[]]></FILTER_PREDICATES>
</row>
<row>
<Operation><![CDATA[INDEX(UNIQUE SCAN) RSHB_UNFR.XPK_PHYSICALUCLIENTSTATUS]]></Operation>
<Optimizer><![CDATA[ANALYZED]]></Optimizer>
<Cost><![CDATA[0]]></Cost>
<Cardinality><![CDATA[1]]></Cardinality>
<Bytes><![CDATA[]]></Bytes>
<Partition_Start><![CDATA[]]></Partition_Start>
<Partition_Stop><![CDATA[]]></Partition_Stop>
<Partition_Id><![CDATA[]]></Partition_Id>
<ACCESS_PREDICATES><![CDATA["CS"."STATUS_ID"=:B1]]></ACCESS_PREDICATES>
<FILTER_PREDICATES><![CDATA[]]></FILTER_PREDICATES>
</row>
<row>
<Operation><![CDATA[SORT(ORDER BY)]]></Operation>
<Optimizer><![CDATA[]]></Optimizer>
<Cost><![CDATA[1926395]]></Cost>
<Cardinality><![CDATA[942348]]></Cardinality>
<Bytes><![CDATA[96119496]]></Bytes>
<Partition_Start><![CDATA[]]></Partition_Start>
<Partition_Stop><![CDATA[]]></Partition_Stop>
<Partition_Id><![CDATA[]]></Partition_Id>
<ACCESS_PREDICATES><![CDATA[]]></ACCESS_PREDICATES>
<FILTER_PREDICATES><![CDATA[]]></FILTER_PREDICATES>
</row>
<row>
<Operation><![CDATA[NESTED LOOPS]]></Operation>
<Optimizer><![CDATA[]]></Optimizer>
<Cost><![CDATA[]]></Cost>
<Cardinality><![CDATA[]]></Cardinality>
<Bytes><![CDATA[]]></Bytes>
<Partition_Start><![CDATA[]]></Partition_Start>
<Partition_Stop><![CDATA[]]></Partition_Stop>
<Partition_Id><![CDATA[]]></Partition_Id>
<ACCESS_PREDICATES><![CDATA[]]></ACCESS_PREDICATES>
<FILTER_PREDICATES><![CDATA[]]></FILTER_PREDICATES>
</row>
<row>
<Operation><![CDATA[NESTED LOOPS]]></Operation>
<Optimizer><![CDATA[]]></Optimizer>
<Cost><![CDATA[1904443]]></Cost>
<Cardinality><![CDATA[942348]]></Cardinality>
<Bytes><![CDATA[96119496]]></Bytes>
<Partition_Start><![CDATA[]]></Partition_Start>
<Partition_Stop><![CDATA[]]></Partition_Stop>
<Partition_Id><![CDATA[]]></Partition_Id>
<ACCESS_PREDICATES><![CDATA[]]></ACCESS_PREDICATES>
<FILTER_PREDICATES><![CDATA[]]></FILTER_PREDICATES>
</row>
<row>
<Operation><![CDATA[TABLE ACCESS(FULL) RSHB_UNFR.PHYSICAL_UCLIENT]]></Operation>
<Optimizer><![CDATA[ANALYZED]]></Optimizer>
<Cost><![CDATA[18803]]></Cost>
<Cardinality><![CDATA[942348]]></Cardinality>
<Bytes><![CDATA[81984276]]></Bytes>
<Partition_Start><![CDATA[]]></Partition_Start>
<Partition_Stop><![CDATA[]]></Partition_Stop>
<Partition_Id><![CDATA[]]></Partition_Id>
<ACCESS_PREDICATES><![CDATA[]]></ACCESS_PREDICATES>
<FILTER_PREDICATES><![CDATA[]]></FILTER_PREDICATES>
</row>
<row>
<Operation><![CDATA[INDEX(UNIQUE SCAN) RSHB_UNFR.XPKUCLIENT]]></Operation>
<Optimizer><![CDATA[ANALYZED]]></Optimizer>
<Cost><![CDATA[1]]></Cost>
<Cardinality><![CDATA[1]]></Cardinality>
<Bytes><![CDATA[]]></Bytes>
<Partition_Start><![CDATA[]]></Partition_Start>
<Partition_Stop><![CDATA[]]></Partition_Stop>
<Partition_Id><![CDATA[]]></Partition_Id>
<ACCESS_PREDICATES><![CDATA["PC"."UCLIENT_ID"="UC"."UCLIENT_ID"]]></ACCESS_PREDICATES>
<FILTER_PREDICATES><![CDATA[]]></FILTER_PREDICATES>
</row>
<row>
<Operation><![CDATA[TABLE ACCESS(BY INDEX ROWID) RSHB_UNFR.UCLIENT]]></Operation>
<Optimizer><![CDATA[ANALYZED]]></Optimizer>
<Cost><![CDATA[2]]></Cost>
<Cardinality><![CDATA[1]]></Cardinality>
<Bytes><![CDATA[15]]></Bytes>
<Partition_Start><![CDATA[]]></Partition_Start>
<Partition_Stop><![CDATA[]]></Partition_Stop>
<Partition_Id><![CDATA[]]></Partition_Id>
<ACCESS_PREDICATES><![CDATA[]]></ACCESS_PREDICATES>
<FILTER_PREDICATES><![CDATA[]]></FILTER_PREDICATES>
</row>
</results>