The Growth



  • We have a VERY large database with countless billions of records. Some of our clients want quicker response times than searching those tables will allow, so some genius decided that we should graft-on a set of parallel tables with identical fields, but for a subset of certain client data. This data would be identical in both tables. Thus, we do something like:

       if (special clients) {
    search duplicate data tables
    } else {
    search main tables
    }

    At the time, I said not to do it because these clients were expecting rapid growth and would exceed their own specifications in short order.

    Nobody listened.

    Fast forward a few months and the client is griping that the queries are taking too long.

    The solution?

    Subdivide the graft-on duplicate data tables into subordinate tables.

    So now, for each of several main tables, we have three copies of the data:

       mainTable
    |
    |--- mainTableDupData
    |
    +----mainTableDupDataSegment1
    +----mainTableDupDataSegment2
    +----mainTableDupDataSegment3
    +----mainTableDupDataSegment4

    ...with the idea that we can add more segments as we need them. This means that our code now looks like this:

       if (special clients) {
    if (id is in segment 1) {
    search duplicate data tables for segment 1
    } else if (id is in segment 2) {
    search duplicate data tables for segment 2
    } else if (id is in segment 3) {
    search duplicate data tables for segment 3
    } else if (id is in segment 4) {
    search duplicate data tables for segment 4
    }
    } else {
    search main tables
    }

    So the main table has a growth that is now sprouting sub-growths of its own... It reminds me of cancer.

    Maybe it's just me. It's not like Oracle supports partitioning data...




  • Did you at least get the naming on these dupe data tables to be something reasonable and reflective of what they are?  Say mainTableTumorSegment1, mainTableTumorSegment2, ...


  • ♿ (Parody)

    Wrong! Let the customers fix up the DB as they like. Easy as forwarding an email!



  • @snoofle said:

    ...
    Wow, your DBA Team seems not to understand the concept of NO. I would have refused to allow something like that into production. As you mentioned, Partitioning with proper constraints would have been the way to go.



  • Wow. The people you for and with are utter lunchmeat!

    Before anyone asks, go buy some sliced bologna, set it on a table in front of you, and then try to reason with it.

    Lunchmeat.



  • Another fine example of the NIH anti-tactic. After all, it's not as if anyone else in the history of data storage would ever have come across this issue and invented a solution for it…



  •  Sounds like somebody needs to learn about data warehousing.

     And by "learn" I mean beaten into them using the cluebat(tm)



  • @galgorah said:

    Wow, your DBA Team seems not to understand the concept of NO. I would have refused to allow something like that into production.
     

     

    Simply saying No & telling management it is a bad Idea is seldem sucsessfull as they will overide it with "its what the customer wants so just do it"

    as well as telling them it is a bad idea you need to be able to propose an alternative solution that will give the customer "What they Want" (in this case faster access) just not necessarily what they ask for.

    without an alternative sioultion you will always be forced to do the wrong thing.



  • @ip-guru said:

    @galgorah said:

    Wow, your DBA Team seems not to understand the concept of NO. I would have refused to allow something like that into production.
     

     

    Simply saying No & telling management it is a bad Idea is seldem sucsessfull as they will overide it with "its what the customer wants so just do it"

    as well as telling them it is a bad idea you need to be able to propose an alternative solution that will give the customer "What they Want" (in this case faster access) just not necessarily what they ask for.

    without an alternative sioultion you will always be forced to do the wrong thing.


     

    Maybe snoofle was too subtle about the alternative here. Oracle has an easy solution called partitioning. http://docs.oracle.com/cd/B10501_01/server.920/a96524/c12parti.htm

    I am guessing snoofle was not subtle at his office.



  • @ip-guru said:

    @galgorah said:

    Wow, your DBA Team seems not to understand the concept of NO. I would have refused to allow something like that into production.
     

     

    Simply saying No & telling management it is a bad Idea is seldem sucsessfull as they will overide it with "its what the customer wants so just do it"

    as well as telling them it is a bad idea you need to be able to propose an alternative solution that will give the customer "What they Want" (in this case faster access) just not necessarily what they ask for.

    without an alternative sioultion you will always be forced to do the wrong thing.

    Implementation details like this should be transparent to management and to clients. They have better things to worry about than schema level changes. To your other point mentioning partitioning is in fact suggesting an "Alternative".

    I would say these kinds of issues could be solved with an actual change management process. As the DBA in my company I sit on the board of change management and am classified as a "Gate Keeper". I do tell people, even our Directors "no". But as you suggest, I also offer alternatives. I rarely however get into technical reasoning with the higher ups.



  •  @ip-guru, @galgorah: I did suggest partitioning to the tech management folks as a solution to the performance problem. They said no. Our DBAs (who, IMHO, are not actual DBAs) pushed for any solution which did not require them to make changes.The tech bean counters decided to use parallel tables and do the home grown thing in spite of my begging to the contrary.

    When it blew up this time, I basically said I told you so, but still they refused to go the partitioning route. So in spite of the fact that the new implementation of table/sub-table/sub-sub-table will, at some point, need to be sub-sub-sub-divided, they still want to go this route. Even after having been burned twice.

    Some people should simply not be allowed to make technical decisions.



  • @snoofle said:

     @ip-guru, @galgorah: I did suggest partitioning to the tech management folks as a solution to the performance problem. They said no. Our DBAs (who, IMHO, are not actual DBAs) pushed for any solution which did not require them to make changes.The tech bean counters decided to use parallel tables and do the home grown thing in spite of my begging to the contrary.

    When it blew up this time, I basically said I told you so, but still they refused to go the partitioning route. So in spite of the fact that the new implementation of table/sub-table/sub-sub-table will, at some point, need to be sub-sub-sub-divided, they still want to go this route. Even after having been burned twice.

    Some people should simply not be allowed to make technical decisions.

    But then who would run your company?



  • @Sutherlands said:

    @snoofle said:

     @ip-guru, @galgorah: I did suggest partitioning to the tech management folks as a solution to the performance problem. They said no. Our DBAs (who, IMHO, are not actual DBAs) pushed for any solution which did not require them to make changes.The tech bean counters decided to use parallel tables and do the home grown thing in spite of my begging to the contrary.

    When it blew up this time, I basically said I told you so, but still they refused to go the partitioning route. So in spite of the fact that the new implementation of table/sub-table/sub-sub-table will, at some point, need to be sub-sub-sub-divided, they still want to go this route. Even after having been burned twice.

    Some people should simply not be allowed to make technical decisions.

    But then who would run your company?
    Someone competant?



  • @galgorah said:

    @Sutherlands said:
    @snoofle said:

     @ip-guru, @galgorah: I did suggest partitioning to the tech management folks as a solution to the performance problem. They said no. Our DBAs (who, IMHO, are not actual DBAs) pushed for any solution which did not require them to make changes.The tech bean counters decided to use parallel tables and do the home grown thing in spite of my begging to the contrary.

    When it blew up this time, I basically said I told you so, but still they refused to go the partitioning route. So in spite of the fact that the new implementation of table/sub-table/sub-sub-table will, at some point, need to be sub-sub-sub-divided, they still want to go this route. Even after having been burned twice.

    Some people should simply not be allowed to make technical decisions.

    But then who would run your company?
    Someone competant?
    At snoofle's company? Shirley, you jest.



  • Don't call me Shirley.



  • @snoofle said:

    When it blew up this time, I basically said I told you so, but still they refused to go the partitioning route. So in spite of the fact that the new implementation of table/sub-table/sub-sub-table will, at some point, need to be sub-sub-sub-divided, they still want to go this route. Even after having been burned twice.

    How are you going for disk space these days?



  • @flabdablet said:

    How are you going for disk space these days?

    Defragment the hard drive. If you don't get enough space back, buy a 10 megabyte memory stick.



  •  How about using the, you know, "index" thingy? Last time I checked, Oracle seemed to support that one, too, at least in the Enterprisey Edition.



  • I once ended up having to maintain a website which had an "ingenious" solution to the data growth problem. Every day, the site ran a number of "games", and for each game there would be many "entries". The games were listed in the "Games" table, and for each game there was an "EntriesNNN" table (where NNN was the game ID). Creating new games via the site's admin interface caused it to run "CREATE TABLE" for each new game.

    You can imagine how much fun this made some of the queries we had to build up...

    (the worst thing was, that wasn't even the most WTFy part of that site's design...)



  • sql = "SELECT AVERAGE(something) FROM Entries1 ";
    gameIds = query("SELECT gameId FROM Games");

    for(gameId : gameIds) {
      sql += " UNION Entries" + gameId;
    }



  • @snoofle said:

    Our DBAs (who, IMHO, are not actual DBAs) pushed for any solution which did not require them to make changes.
     

    Because DBAs know nothing about optimising data access, so this isn't really their area of expertise... right?

    Given the history of your DBAs, I'm guessing they're pushing for this quite simply because they have no clue as to what to do.

    @snoofle said:

    they refused to go the partitioning route.

    Untrue. They did partition their tables.

    They just didn't use Oracle's features to perform it, but invented their own circular rotating transportation solution.


Log in to reply