which is the better one among these?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: which is the better one among these?

  1. #1
    Join Date
    Dec 2000
    Posts
    95

    Question

    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?





  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Could you explain more in details about Nested Tables?

  3. #3
    Join Date
    Jun 2000
    Location
    Conway,AR,USA
    Posts
    29
    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) */
    column_1,
    column_2,
    ..
    column_10
    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.
    Soumya
    still learning

  4. #4
    Join Date
    Dec 2000
    Posts
    46
    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
    behavior).

    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...

    --

  5. #5
    Join Date
    Sep 2000
    Posts
    155

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width