ThatJeffSmith

Tag: data modeling

Modeling Function Based Indexes in Oracle

@thatjeffsmith Is there a way to define a function-based index in #SQLDevModeler? — Kent Graziano (@KentGraziano) October 29, 2014 Function based indexes [DOCS] have been part of Oracle Database since at least version 8. Instead of just indexing someone’s name, you could also index the values returned by a function call over that name. Example […]

Data Modeling via Excel?

In SQL Developer if you use the Import Data feature from the main tree node, it will create a new table based on a delimited text or Excel file. We’ll look at the data and create some columns for you. In version 4.1, we’ll recognize numbers as numbers, dates as dates, and try to get […]

Viewing Complex Types in Oracle SQL Developer Data Modeler

Instead of having a column of type VARCHAR2 for a ‘Country,’ you might want to create an COUNTRIES type. Said COUNTRY would have an ID, NAME, and LOCATION. To make things even more fun, LOCATION would also be a custom type of LOCATIONS, having even more attributes/columns. To model these from scratch, you would build […]

My Favorite SQL Developer Data Modeler Features

I’m doing a talk on this very subject next week at ODTUG’s KScope14 conference, and I’ll put up the slides on Slideshare after the show, but I wanted to give a quick shout-out to the things you COULD Be using today to get the most out of your models and design experience. Search SubViews Reports […]

Configuring Memory Usage in Oracle SQL Developer

Sometimes you need more memory. Here’s an example: you want to model a database that has 1500 tables in it. What would that even look like? If you zoom out far enough… Now, ‘large’ is a subjective term. But lets put it this way – if you’re dealing with more than several hundred tables or […]

Oracle SQL Developer: Configuring Object DDL Scripts

One of the more popular features in SQL Developer is the ability to generate the CREATE DDL script for any object. If you open an object editor, the SQL page shows this script. Now, what many of you don’t realize is that the way this code can be generated is configurable. The requests I am […]

Finding Calculated View Columns

Imagine you have 300 or so views, and within those views, are a bunch of business rules. Like, we’re going to calculate someone’s salary as their salary + their (commission percent * sales). Now imagine your views, and their columns, aren’t documented. They’re not documented in the data model, and they’re not documented via the […]

What’s Wrong with this Create Table Statement?

I recently got this request for help via a YouTube video comment. I’m sharing it here, because I see this type of question A LOT. And it scares me. “When I run this script it reads = Error starting at line : 1 in command – What am I doing wrong? Before I show the […]