this is my question
I have a table with 10 columns.
let assume that the table contains 30 lachs of records (rows) , then querying from that table will be slow because of huge no of rows.
so to avoid this i have two options
one is using nested tables and the other one is partitioning the table into parts using partition tables.
so please help me which is the best one at performance level?
Could you explain more in details about Nested Tables?
There is some solution
1. If you query on a column/columns you can think of creaing an index on the column/columns.
2. You can user the parallel option (if your server is set to do it) i.e. in the CREATE table statement mention parallel(DEGREE n) and in the select statement you can use
ALTER SESSION ENABLE PARALLEL DML
SELECT /*+ parallel(a,3) */
FROM table_1 a
This opens 3 DML sessions instead of 1 and the fetch may be faster.
3. You can partition the table data across various tablespaces (if you have the option)
These three solutions are general. You'll have to test them
to get the performance.
Don't be scared of full table scans sometimes its the best choice.
There are times where performance improved doing full table scans.
The new optimizer in oracle 8i prefers full table scan (cost based
optimizer) if the query is going to return more than 4-7% of the
table (of course you can use the index hint to override this
You might want to consider the selectivity of the column you are
going to use before you create an index. If its a low selectivity
column like gender, you might be better off doing a full table scan
rather than a index scan followed by a table scan.
Partitioning is a good option. But you may have to do some more
research before you commit yourself to it (you have to have a plan
with your existing data, updates to partitioning key (some versions
of oracle don't allow you to update the partitioned key) etc...
Few questions on PARALLEL issue.....
Would be greatful for all your input on each of these questions below.
Can I use PARALLEL option in the CREATE TABLE or ALTER SESSION for an OLTP application OR is this advised only for DSS or DSD applications?
Is is mandatory to use PARALLEL option only if there are more than one CPUs?
Is PARALLEL option same as PARALLEL QUERY (we specify some init.ora parameters to TRUE) option or different? If different, in what way?
Thanks in advance!
Click Here to Expand Forum to Full Width