Index performance between two modelization
Hi,
I have some questions on Oracle index performances between two modelizations.
Please consider the "classic model" below:
TABLE: PEOPLE (ALL DATA ARE FICTITIOUS)
| ID (PK) | NAME (VARCHAR2) | REFERENCE (VARCHAR2 + UNIQUE CONSTRAINT) | BIRTHDAY (DATE) | FAVORITE_NUMBER (NUMBER) | GENDER (VARCHAR) | IS_HAPPY (BOOLEAN) | SALARY (NUMBER) |
|---|---|---|---|---|---|---|---|
| 1 | SMITH | 156874 | 01/02/1985 | 45 | male | true | 3874 |
| 2 | BROWN | 197453 | 05/08/2001 | 124 | female | true | 2985 |
| 3 | TAYLOR | 794412 | 03/09/1968 | 78 | female | false | 2852 |
| 4 | ZONZI | 485412 | 24/09/1999 | 2 | male | true | 1234 |
...
Please consider the less classic model below:
| PEOPLE_ID | CARACTERISTIC_TYPE (VARCHAR2) | VALUE (VARCHAR2) |
|---|---|---|
| 1 | NAME | SMITH |
| 1 | REFERENCE | 156874 |
| 1 | BIRTHDAY | 01/02/1985 |
| 1 | FAVORITE_NUMBER | 45 |
| 1 | GENDER | male |
| 1 | IS_HAPPY | true |
| 1 | SALARY | 3874 |
| 2 | NAME | BROWN |
| 2 | REFERENCE | 197453 |
| 2 | BIRTHDAY | 05/08/2001 |
| 2 | FAVORITE_NUMBER | 124 |
| 2 | GENDER | female |
.. and so on
The main interest of the second model is to be able to add a new characteristic without needing DDL operation. I am convinced that it is not performance compliant but I don't know how to evalute the gap between the two models.
1