Dictionary CACHE

Database

Dictionary CACHE

I have created 1 table
create table emp
(emp_no integer,
emp_loc char(8),
dept_no integer
)
unique primary index (emp_no)
inserted 1 row in it....

then i have created 1 more table
create table emp1
(emp_no integer,
emp_loc char(8),
dept_no integer
)
unique primary index (emp_no)

When i am running
insert into emp1 sel * from emp
explain plan gives
3) We lock emp1 for write, and we lock emp for
read.
4) We do an all-AMPs MERGE into emp1 from emp.
5) We spoil the parser's dictionary cache for the table.

Why we need to spoil parser's cache here???

I created 1 more table(emp3) with different UPI(dept_no)
and ran insert into emp3 sel * from emp
Explain gives
3) We lock emp3 for write, and we lock emp1 for
read.
4) We do an all-AMPs RETRIEVE step from emp1 by way of an
all-rows scan with no residual conditions into Spool 1 (all_amps),
which is redistributed by hash code to all AMPs. Then we do a
SORT to order Spool 1 by row hash. The size of Spool 1 is
estimated with low confidence to be 340 rows. The estimated time
for this step is 0.00 seconds.
5) We do an all-AMPs MERGE into emp3 from Spool 1 (Last
Use).
Here no dictionary cache is spoiled...

We know that a parser will spoil the dictionary cache when we issue any DDL statement......
Here no DDL stmnt was issued still parser spoils the cache....??
3 REPLIES
N/A

Re: Dictionary CACHE

The parser also spoils the dictionary when it assumes that the number of rows changed significantly, usually for insert select and delete/update using non-index columns.

Dieter

Re: Dictionary CACHE

I have one query which is insert - select join is on PI column query is not performing very well. what should be done to avoid this.

Thanks

Re: Dictionary CACHE

There could be alot of reasons for the bad performance. The index selection of the target table could be different than the source table causing the redistribution of data on the AMPs resulting in bad performance.

Without looking at the table structures, your query and the explain plan, it is not possible to pin pout the issue. Share these things to help you figure out the issue!