Post reply

Index on Partitioned View

  • November 2, 2007 at 9:13 am

    #62878

    I have archive table and a 'live' table with the same table struture. I have created a view with a union of these 2 tables that is required for reporting. I cannot create a index on the view due to the UNION restriction. What are my other options ?

    This is the DDL of the table

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [GLID] [varchar](20) NULL,

    [AccountID] [int] NULL,

    [Trx_Date] [datetime] NULL,

    [Debit] [decimal](19, 2) NULL,

    [Credit] [decimal](19, 2) NULL,

    [Source] [varchar](50) NULL,

    [Operation] [varchar](50) NULL,

    [GLTrxType] [tinyint] NOT NULL ,

    [GLTrxID] [int] NOT NULL ,

    [CreateDate] [datetime] NULL,

    [CreateUser] [varchar](20) NULL

  • November 2, 2007 at 9:30 am

    #747821

    Indexed views will persist the result of the view (as if it were a table). Assuming that the archive table is large, and the view is literally just the union of the two tables, is this really what you would like to do?

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • November 2, 2007 at 9:57 am

    #747839

    Indexed views will persist the result of the view (as if it were a table).

    Does it mean that the index of the base table will be used when querying the view ?

  • November 2, 2007 at 2:26 pm

    #747931

    I looked at the query execution plan for the view and it is using the underlying indexes of the tables.

    Thanks for your input.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply