Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    205

    Unanswered: Equivalent for the below query ?

    Please can any one give the Oracle equivalent for the below SQL Server query ?

    SQL Server :
    ------------------
    CREATE view view_test AS SELECT * from test_table alias1 (INDEX = pk_constraint)

    Here pk_constraint is the primary key constraint (name) defined in the table 'test_table'

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Interesting: what does this (INDEX = pk_constraint) actually do? What would be wrong with the view if you left it out?

  3. #3
    Join Date
    Mar 2004
    Posts
    205
    I am not sure of it. Anyway I will what I understood out of it.

    INDEX = pk_constraint is given, for optimization plan during query execution.

    Is there anything like this in Oracle ?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    In 9i and above there is syntax like this:

    CREATE view view_test
    (col1, col2, col3,
    CONSTRAINT pk_constraint PRIMARY KEY (col1) RELY DISABLE NOVALIDATE) AS SELECT * from test_table alias1;

    This type of constraint is for information only, it is not enforceable. It could be used by the optimizer in query rewrites. However, it is not normally necessary to do this for performance, because queries against views are turned into queries against the base tables anyway, and the appropriate indexes will be used.

  5. #5
    Join Date
    Apr 2004
    Location
    USA
    Posts
    33
    Quote Originally Posted by SamCute
    Please can any one give the Oracle equivalent for the below SQL Server query ?

    SQL Server :
    ------------------
    CREATE view view_test AS SELECT * from test_table alias1 (INDEX = pk_constraint)

    Here pk_constraint is the primary key constraint (name) defined in the table 'test_table'

    You can try the following:

    create view view_test
    as
    select /*+ index(alias1 pk_constraint)*/
    * from test_table alias1;

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The hint is a possibility, but generally it is best not to use hints.

  7. #7
    Join Date
    Mar 2004
    Posts
    205
    Dear ArvindRam,

    I couldn't get your query...What it implies ?
    /* .... */ - what is this implies inbetween a select query ?

  8. #8
    Join Date
    Apr 2004
    Location
    USA
    Posts
    33
    It is an optimizer hint

    check out: http://download-east.oracle.com/docs...tsref.htm#5156

    You probably will need to register and login before you can access the information.

    But like Tony points out if might not be a good practice in all cases (only if your view is performing very poorly).

Posting Permissions

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