Hi,
Win2000, Ora 8.1.7.
Enough mem.
The total story:
- I started having 1 instance called PREV and 1 (default/auto created)
instance (scott/tiger) called ORCL
- In PREV, I imported the PREVENT scheme containing about 30 tables
- I analyzed (COMPUTE) the PREVENT scheme
- I query from only 2 tables of that PREVENT-scheme (one -DISTRICT- having
20 records, the other -DISTRICT_STREET- 2000).
- When I select from the PREV database by means of:
"select id_district, district_name FROM district, district_street where
(district_id=id_district) AND (place_street_id=707)"
the performance is BAD AND the sort of generated records is random!
The execution plan:
SELECT STATEMENT Cost = 5
SORT UNIQUE
HASH JOIN
TABLE ACCESS FULL DISTRICT
INDEX FAST FULL SCAN DISTRICT_STREET_UNIQUE
- In the scott/tiger ORCL database I created a dbLink to PREV
- In the scott/tiger ORCL database I created the two tables by means of:
"create table district as select * from district_at_rem_scheme"
"create table district_street as select * from district_street_at_rem_scheme"
I also created the index "DISTRICT_STREET_UNIQUE"
- I analyzed (COMPUTE) the scott scheme
- When I select from the ORCL database by means of:
"select id_district, district_name FROM district, district_street where
(district_id=id_district) AND (place_street_id=707)"
the performance is GOOD AND the sort of generated records is always the
same!
The execution plan:
SELECT STATEMENT Cost = 5
SORT UNIQUE
HASH JOIN
INDEX FAST FULL SCAN DISTRICT_STREET_UNIQUE
TABLE ACCESS FULL DISTRICT
- In the PREV database I created a dbLink to ORCL
- In the PREV database I created two tables by means of:
"create table district2 as select * from district_at_rem_scheme"
"create table district2_street as select * from district_street_at_rem_scheme"
I also created the index "DISTRICT_STREET_UNIQUE2"
- I analyzed (COMPUTE) the PREVENT scheme
- When I select from the PREV database by means of:
"select id_district, district_name FROM district2, district_street2 where
(district_id=id_district) AND (place_street_id=707)"
the performance is GOOD AND the sort of generated records is always the
same!
The execution plan:
SELECT STATEMENT Cost = 5
SORT UNIQUE
HASH JOIN
INDEX FAST FULL SCAN DISTRICT_STREET_UNIQUE
TABLE ACCESS FULL DISTRICT
If I query in PREV on the 2 tables the performance is BAD and sort of
result-set is RANDOM.
If I create 'copy' tables in PREV instance by means of "create table ... as
select from ...." and query on them the performance is GOOD and the sort of
the result-set is always the same. (so the same query in the same instance
performs different on original tables than on copy of that tables)
Please help...
Stephan
Received on Wed Jan 01 2003 - 11:02:31 CST
