Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2003
    Posts
    148

    Unanswered: Opinions on views on the app side

    Hi all. I just want some opinions on an idea. I have an app. that will probably run across a large geographical area on my corp. intranet. There is currently a verison of this app in existance, and pages load a bit slow for my taste because of the sheer size of the table, and sorts being performed on them. The network latency isnt really helping much, either. My question is the following - i know DBA's frequently use views to enforce rules and security for end users - to limit what they can see amongst other things. My company will splits the geographic area this app runs in into 4 or so sections. Does anyone see an issue for me setting an application variable to determine what view they use? IE, section 1 will have a view that only allows them to view data pertinent to section 1. Theoretically, 1/4 of the data. My second question is...Will I necessarily see a performance enhancement doing this? Or is the view just going to have to parse the entire table anyway?

    Any suggestions or opinions appreciated.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I don't think that creating views will address any of your concerns. Essentialy, the two scenarios below:

    Code:
    create view v_section1 as select * from mytable where section=1;
    select * from v_section1;
    and
    Code:
    select * from (select * from mytable where section=1);
    are identical. You could use materialized views but then you'll face a whole new issue of refreshing them.

  3. #3
    Join Date
    Dec 2003
    Posts
    148
    Ah. That is what I was afraid of...Thanks for the info!

  4. #4
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    If you know that you are almost always interested in specific sections, then yo can partition the table using "section" as the key.

    Thus, the query will have to look at 1/4 th the number of rows on average.

    I am assuming that there is an index on the section column. If not, creating an index on the section column might help, or might not.

    By the way, how large is your table?

    Ravi

  5. #5
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Views will usually follow the standard rules of optimisation, regardless of how you write them.

    A common way of dealing with this is to use a package with some global variables which are defined through some accessor functions. By their very nature functions are difficult for Oracle to optimise (even with the WNDS etc pragmas).

    Consider the following Pseudocode
    Code:
    pck_global is
        Section constant number(1);
        function Get_Section (WNDS,RNDS, WNPS) is return Section;
        procedure Set_Section( val number) Section := val;
    end pck_global;
    
    create or replace view test_view as
       select * from table where section = pck_global.get_section;
    Your user connects and executes the PCK_GLOBAL.Set_Section(1).

    When they query the view, Oracle doesn't know what the function is doing. Regardless of the Pragma's, it doesn't know if the return value will change according to some arbitrary condition etc, this function/view is inefficient.

    Another way to deal with this is through some additional columns on a temp/user table which define the section. These can be optimised fairly well due to the read consistency (the value was read from a table, therefore Oracle will maintain consistency, therefore it can be deduced that the value will not change during the select).

    I strongly suggest that you look at histograms, and Materialised Views (snapshots) to help you in your quest. Oracle also has FGA (Fine grained access control) but I doubt this will suit your needs.

    Introduction to Sql tuning (views do this too) http://www.billmagee.co.uk/oracle/sqltune/index.html

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  6. #6
    Join Date
    Dec 2003
    Posts
    148
    Hey all, thanks for the responses.
    Raji - my table is about 120k columns x 17 columns. It is not obscenely large by any means, but the table size increases at a very rapid rate. (4-5k/month)
    If I partioned into the "Sections" let's just say N,S,E, and W for hypothetical purposes...would I encounter any negative impact from this? THat is, can I address all 4 partitions in a single query? Im sure this is an obvious question, but I have limited experience with partioning tables.

    Bill - thanks for the information. I'm a bit weary of materialized views at the moment...The table I'm focusing on is continually viewed as well as written to and updated...I don't want to have to deal refreshing as it will be nightmarish on an app that is going to be [over]utlized. There is a field in the table that specifies what "Section" the record belongs to. I set up some record paging (by rownum) in the stored procedure which calls these records to the web front end...though it is considerably faster than my first attempt, it just isn't where i'd like it to be. Also, as a point of comparison this application was formally on SQL server [was migrated to 9i] Any other ideas in terms of speed?

    Thanks again everyone for your responses.

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I doubt you will have any problems with partioning as there is no noticable overhead and many benefits, especially if you have local indexes. Also it is transparent to your app (you dont need any code changes) in that you can query data across all partitions (just dont specify section in your query).

    As for comparison with sqlserver you will probably find that sqlserver is faster when you have a low number of users (and you dont have contention on the same bits of data), but Oracle should be faster as the number of users and contention increases. You can think of it that Sqlserver/Sybase where designed for the best case scenario (low user, low contention) and Oracle was designed to handle the worst case scenario (lots of users and lots of contention).

    Alan

  8. #8
    Join Date
    Dec 2003
    Posts
    148
    In that case, I will be exploring my options in terms of partitioning, sounds pretty good at the moment. Thank you everyone for your informative and valuable responses, enjoy your day.

  9. #9
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    Partitioning is "physical" while queries are "logical".

    That is, partitioning pertains to the physical storage of the data and improves performance if designed properly.

    A query is a logical request/command to the database management system telling it what data you want.

    The physical implementation of a logical concept can, by definition, have no effect on the logical constructs (queries).

    Therefore, have no fear. Partitioning will not force you to change any queries or any code.

    Ravi

Posting Permissions

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