Completeness vs database efficiency

Partitioning addresses key issues in supporting very large tables and indexes by letting you decompose them into smaller and more manageable pieces called partitions. SQL queries and DML statements do not need to be modified in order to access partitioned tables. However, after partitions are defined, DDL statements can access and manipulate individuals partitions rather than entire tables or indexes. This is how partitioning can simplify the manageability of large database objects.”
An Oracle database concept on VLDB (Very Large Databases).

Every OSS that I’m aware of is back-ended by some form of database for storing the vast amounts of information they collect. There are all sorts of database efficiency mechanisms used by DBAs (Database Administrators), but you sometimes also need to take a closer look at the application design and ways that you’re modelling the network.

Years ago, I was modelling an ATM switch to allow customer services to be created across it. Each physical port supported a VPI range of (0-255) and each VPI supported a VCI range of (1–65535). I modelled all the possible port ranges so that any VPI/VCI variation could be assigned to any given customer. Without thinking, I then bulk-loaded all of these logical ports across the customer’s ATM network. I’m sure you’ll be clever enough to see why my data load was still going when I came back to work the next day. I was effectively loading nearly 17 million (256*65535) new logical ports for every single physical port in the ATM network. Doh! Do you think the ports table in the database was a little bit big after that blunder??? πŸ™‚ Doing a SELECT or a simple implicit JOIN using this table would’ve bought things to a stand-still for a while.

Luckily it was only a data-mig environment and I was able to trash the ports table without causing any harm.

Rather than modelling every VPI/VCI variation, we didn’t pre-build any. Instead, we simply changed the service order activities to create the required VPI/VCI for each new customer before then allocating their service.

Ever since, I’ve been careful to trade off completeness of the model against keeping data to a minimum to help the efficiency from a database and application usability perspective.

If this article was helpful, subscribe to the Passionate About OSS Blog to get each new post sent directly to your inbox. 100% free of charge and free of spam.

Our Solutions


Most Recent Articles

2 Responses

  1. Oh good. Some proper data modelling.

    The question to ask is: What is the benefit of having a database object representing un-allocated resources? In this case, presumably zero. The resource is a range of known and consistent integers. You just need to know which values have been already been assigned, and to which physical resource/service.

    For limited resources, that may vary in quantity or configuration in their unallocated state, then an object is probably the right way to go, e.g. physical ports – you need to know how many, where they are, what speed they support etc etc.

    The harder question for me in this case: Should the VPI/VCI be an object at all, or just a parameter against, say, a service or circuit?


  2. Hi James

    Great questions.
    The main reason for having resources pre-allocated is that it slightly simplifies the service order entry and service design processes. It also ensures that the designer doesn’t choose a vpi or vci that is outside the equipment’s supported range (thus reducing the chance of provisioning errors). The service entry team usually won’t subject matter experts on the networks but an alternative is to introduce range-binding logic into the OSS if the OSS supports it.

    Yes, there’s definitely the option of storing vpi/vci as a service attribute. There are a couple of reasons why the ‘logical port’ attribute is a good alternative though:
    If a service attribute, you’re only storing the vpi/vci at the customer edge of the ATM cloud rather than tracing the whole circuit through the cloud (ie you lose visibility of utilisation inside the cloud). The second is closely related in that you can query utilisation / capacity more easily from logical ports across all network devices than picking out fields from the service attributes table and joining with inventory tables (depending on the schema of the particular OSS in question of course). Service order-related tables and relationships can be a bit complex, which can make them a little inefficient to query. It’s ideal if you can keep capacity planners away from querying these tables! πŸ™‚

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.